4

I am trying to auto-populate dates from a fixed date in Google Sheets.
Essentially, I want it to auto-populate the dates for every Tuesday, Thursday and Saturday.

I have found stuff on skipping weekends, but nothing like this and I am stumped - which is not hard to do!

3

In the enumeration used by weekday function by default, Tuesdays, Thursdays and Saturdays are days 3, 5, and 7 of the week. The solution I give below is not special to these particular days: you can replace {3,5,7} below with any other selection of days of week.

=A2 + 1 + min(arrayformula(mod({3,5,7} - weekday(A2) - 1, 7)))

This is a formula that would appear in A3, computing the next date based on A2. The starting date at A2 doesn't have to be in the acceptable range itself.

Explanation:

  • A2 + 1 is tomorrow, the minimal possible value of A3. We want to find out if this value is acceptable; if not, by how much it should be increased.
  • arrayformula(mod({3,5,7} - weekday(A2) - 1, 7)) find the day intervals between tomorrow and the acceptable days of week. The mod function is needed to wrap things up correctly: e.g., mod(3 - 6, 7) = mod(-3, 7) = 4
  • Finally, min determines the smallest of those day intervals, and adds it to the value of tomorrow.
1
  • Glad it worked. There is a checkmark to the left of the post that can be used to indicate this fact.
    – user79865
    Sep 29 '15 at 22:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.