1

I have rows of tasks in Google Sheets - some that will repeat weekly and others that repeat monthly. I want to predict the next year of tasks, but can't seem to merge the weekly/monthly pattern without running into issues.

I realize can do this manually (i.e. copy/paste them each separately, then sort by date) but want a way to do this programmatically, so as I add new tasks, they fall in line without having to redo the manual process.

I have tried a few things with query and with arrayformula but can't seem to get either to extrapolate future dates.

screenshot

| improve this question | | | | |
1

You can do it using the combination of ARRAYFORMULA, and a function like ROW(), unanchored minus anchored, to provide an ongoing counter of periods. Let's say you want to start from today (or any other date for first of the series) in A46. Then the following formula will give you the next few days:

enter image description here

To build from here into different date intervals/patterns, use, for example:

=ARRAYFORMULA(TODAY() + (ROW(A46:A48)-ROW(A$46)) * 7)

to get the list of dates separated by a week. Or you can use a function like

=ARRAYFORMULA(
   DATE(
     YEAR(TODAY()),
     MONTH(TODAY()) + ROW(A46:A48)-ROW(A$46),
     DAY(TODAY())
   )
 )

To get the list of dates separated by a month each. The approach can also work with more sophisticated date patterns generated, for example, by WORKDAY.INTL and such.

| improve this answer | | | | |

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.