This post is all about generating all the dates of a month with a single formula. Read along to learn how.
Consider an input as the first day of a month as given in cell I2:
First, let’s calculate the number of days (in I3) since we need to know how long we need the number of rows to populate to use in the SEQUENCE function (we’ll get to that step later in this post).
For this, let us use the EOMONTH function. The EOMONTH function is used to return the last date of a month, in the past or future.
=EOMONTH(I2,0)-I2+1Syntax of this function is
EOMONTH here gives the last date of the current month (note the second argument is 0)
Now, we have the number of days we need and the starting date now, we can use the SEQUENCE function to generate all the dates of a month.
The SEQUENCE function takes as input, the rows to create, columns to create (optional), starting point (optional), and the steps to increment each time (also optional). Let us look at this as a syntax:
In our case the formula to generate all the dates of a month would be
=SEQUENCE(I3, 1, I2, 1)This returns the sequential dates from cell I6 for the month of Jan-24 in a single column as below
With this method, generate a sequence of continuous days easily within seconds.
We have a dedicated video on generating continuous series of dates under different scenarios, do check it out.
