How to autofill dates using arrayformula

Question

I'm using Google sheets for data entry that auto-populates data from my website whenever someone submits to a form. The user's data imports into my sheet with a timestamp (column A).

Using the Arrayformula function, I'd like a column to autofill all the dates of a timestamp within that month. For example, if 1/5/2016 is entered as a timestamp, I'd like the formula to autofill in the dates 1/1/2016 - 1/31/2016.

Additionally, I'd like other months added in the Arrayformula column. For example, if both 1/5/2016 and 2/3/2016 are entered in column A, I'd like the formula to fill in the dates from 1/1/2016 - 2/29/2016.

I know I can manually write in the dates and drag them down the column, but I have a lot of sheets, and using an Arrayformula will save me a lot of time. I've tried a similar formula in column B, but it doesn't autofill in the date gaps. Is what I'm looking for possible?

Here's a copy of the editable spreadsheet I'm referring to: https://docs.google.com/a/flyingfx.com/spreadsheets/d/1Ka3cZfeXlIKfNzXwNCOWV15o74Bqp-4zaj_twC3v1KA/edit?usp=sharing

Answer 1

Short answer

Cell A1

1/1/2016

Cell A2

=ArrayFormula(ADD(A1,row(INDIRECT("A1:A"&30))))

Explanation

In Google Sheets dates are serialized numbers where integers are days and fractions are hours, minutes and so on. Once to have this in mind, the next is to find a useful construct.

• INDIRECT(reference_string,use_A1_notation) is used to calculate a range of the desired size by given the height as a hardcoded constant, in this case 30. You should not worry about circular references in this construct.

• ROW(reference) returns an array of consecutive numbers.

• A1 is the starting date.

• ADD(value1,value2). It's the same as using +. As the first argument is a scalar value and second argument is an array of values, it returns an array of the same size of the second argument.

• ArrayFormula(array_formula) displays the values returned by array_formula

• As A1 is a date, by default the returned values will be formatted as date too.

Answer 2

nice. thanks.

to get the list length adapt to the number of days in the selected month simpy replace the static 30 by eomonth(A1;0)-A1+1. For instance February has only 28 or 29 days.

=ArrayFormula(ADD(A1,row(INDIRECT("A1:A"&eomonth(A1;0)-A1))))

Answer 3

I have an alternative to the above, which allows you to edit only the first row, then add protection (as I like to do with the entire first row where I use this approach for other formulas):

=ARRAYFORMULA(
  IF(
    ROW(A1:A) = 1,
    "Date",
    IF(
      ROW(A1:A) = 2,
      DATE(2020, 1, 1),
      DATE(2020, 1, 1) + (ROW(A1:A) - 2)
    )
  )
)

// pseudo code!
const START_DATE = 2020-01-01

if (currentRow == 1)
  print "Date"
else if (currentRow == 2)
  print START_DATE
else 
  print START_DATE + (currentRow - 2)

Notes:

• the initial date is hard-coded (ensure that the two instances match!)
• ROW(A1:1) returns the current row number, so the first if statement evaluates as "if this is Row 1, then render Date"
• "if this is row 2, render the hard-coded date"
• (nB: adding an integer to a date adds a day)
• "else increment the date in A2 by the (adjusted) number of rows" (the minus two accounts for the two rows handled by the first two ifs (A1 and A2). Eg: in row 3, we want to add 1 to the date in row 2, so current:3 - 2 = 1.

Here's a live example (I added conditional formatting to even months to assist sanity checking that the last day of month is correct):

https://docs.google.com/spreadsheets/d/1seS00_w6kTazSNtrxTrGzuqzDpeG1VtFCKpiT_5C8QI/view#gid=0

Also - I find the following VScode extension handy for syntax highlighting Google Sheets formulas: https://github.com/leonidasIIV/vsc_sheets_formula_extension

The Row1 header trick is courtesy of Randy via https://www.tillerhq.com/what-are-your-favorite-google-spreadsheet-party-tricks/

Answer 4

Increment by Month

If anyone wants to be able to increment by month, here's a way I've been able to accomplish that. Your solution @ptim got me on the right track, thanks.

Formula

Placed in B1

First_Month = 2020-11-01 [named range]

=ARRAYFORMULA(
    IF(
        ROW(A:A) = 1,
        "Date",
        IF(
            LEN(A:A),
            EDATE( First_Month, ROW( A:A ) -2 ),
            ""
        )
    )
)

Result

ID      Month
1       2020-11-01
2       2020-12-01
3       2021-01-01
4       2021-02-01
5       2021-03-01