CODEWITHSUNDEEP
google-sheetsarray-formulas
user4282987
user4282987

Reputation: 75

How to autofill dates using arrayformula

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

Upvotes: 7

Views: 17131

Answers (5)

James Sperry
James Sperry

Reputation: 1

Updated for 2022:

This can now be done pretty easily with the SEQUENCE function, it's also a bit more adaptable.

Below will list all of the days in columns but you can swap the first 2 values to place in rows instead:

=SEQUENCE(1,7,today()-7,1)

More specific to your example, below will take the date entered (via cell, formula, or named cell) and give you the full month in columns:

=SEQUENCE(1,day(EOMONTH("2016-1-5",0)),EOMONTH("2016-1-5",-1)+1,1)

Upvotes: 0

Berteh
Berteh

Reputation: 73

nice. thanks.

To get the list length to adapt to the number of days in the selected month simply replace the static 30 by eomonth(A1;0)-A1. This accommodates for months with 31 days, and for February which can have either 28 or 29 days.

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

Upvotes: 2

Craole
Craole

Reputation: 31

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

Upvotes: 3

ptim
ptim

Reputation: 15587

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/

Upvotes: 1

Wicket
Wicket

Reputation: 38130

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.

Upvotes: 12

Related Questions