Google-sheets – Extrapolate monthly and weekly dates in Google Sheets

google sheets

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.

Best Answer

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:

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.

Regression

With the following function, one can retrieve the linear regression coefficients of two sets of data:

LINEST(y-axis,x-axis) ==> y=ax+b

The second set of data is the accompanying x-axis. This can be a normal numbering, as shown in the first sheet of the example file.

Once the regression factors are known, one can extrapolate any unknown x or y beyond the known set of data:

known x: ax+b
known y: (-b+y)/a

Since R²=1, the numbers given are exact values. I formatted the values to be rounded in the example, because the calculation shows floating point errors.

Dragging

Google has a way of predicting the outcome of a series, based on that series. If you drag the series like shown in the screenshot, then the outcome will appear automatically: enter image description here

Example

I've created an example file for you: Extrapolate
A more practical example: https://webapps.stackexchange.com/a/35668/29140

Remark

The regression method will give you always a value, based on the factors. The more un-linear the sets of data are (R²<1), the more predicted they are.

Google-sheets – List of dates of current and previous month in Google Sheets

With the following formula you can construct a month table, containing all days of the month.

Formula

=ARRAYFORMULA(
   DATE(
     YEAR(NOW()),                                                    // first
     A1,                                                             // second
     ROW(                                                            // third
       INDIRECT("A1:A" & DAY(EOMONTH(DATE(YEAR(NOW()), A1, 1), 0)))
     )   
   )
 )

 copy / past
 =ARRAYFORMULA(DATE(YEAR(NOW()),A1,ROW(INDIRECT("A1:A" & DAY(EOMONTH(DATE(YEAR(NOW()), A1, 1), 0))))))

Screenshot

enter image description here

Explained

The DATE formula is used to construct the date out of the three integers it needs. The first is the YEAR, being calculated from the NOW formula. The second argument is the month that you want to calculate the days from. The third argument must be a day number. Because we're using an ARRAYFORMULA, this is fed by a ROW range. The range of the rows, is dependent upon the amount of days per month. This number is calculated as follows:

DAY(                  // days
  EOMONTH(            // days till end of month in date notation
    DATE(
      YEAR(NOW()),    // from year 
      A1,             // from month
      1               // from day    
   )
 )

Example

I've created an example file for you: Days of Month

Best Answer

Related Solutions

Google Sheets – How to Extrapolate Data

Regression

Dragging

Example

Remark

Google-sheets – List of dates of current and previous month in Google Sheets

Formula

Screenshot

Explained

Example

Related Topic