Google-sheets – Autopopulate Tuesdays, Thursdays, Saturdays starting from a fixed date

google sheetsgoogle-sheets-dates

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!

Best Answer

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.

Formula

=JOIN(
   CHAR(10),
   ARRAYFORMULA(
     QUERY(
       Transactions!A2:D, 
       "SELECT B WHERE A = date '" & text(Summary!A2,"yyyy-MM-dd") & "'"
     ) 
     & ": " &   <=== concatenate
     QUERY(
       Transactions!A2:D, 
       "SELECT D WHERE A = date '" & text(Summary!A2,"yyyy-MM-dd") & "'"
     )
   )
 )

 copy/paste         
 =JOIN(CHAR(10),ARRAYFORMULA(QUERY(Transactions!A2:D, "SELECT B WHERE A = date '" & text(Summary!A2,"yyyy-MM-dd") & "'") & ": " & QUERY(Transactions!A2:D, "SELECT D WHERE A = date '" & text(Summary!A2,"yyyy-MM-dd") & "'")))

Explained

The two QUERY functions, both deliver each part of the data you want to present: the description and the value. These two are then "glued" together, via the ampersands and per row, via the ARRAYFORMULA. Each row is then joined, via the JOIN function, using a line feed (CHAR(10)) as delimiter.

Screenshot

enter image description here

Note

The huge benefit over a script is the immediate re-calculation of the data, when changes occur. Just copy the formula downwards to get all the results. You can add your own logic to remove empty fields.

Example

I've prepare an example file for you: Notes

Google Sheets – How to AutoFill Dates but Skip Weekends

This can be done with a conditional statement involving WEEKDAY

Let's say you put 5/7/2015 in A2, and the conditional formula

=IF(WEEKDAY(A2)=6,A2+3,A2+1)

in A3. (Weekday=6 means Friday, after which two days are skipped). Dragging this down you'll see

5/7/2015
5/8/2015
5/11/2015
5/12/2015
5/13/2015
5/14/2015
5/15/2015
5/18/2015

and so on.