Indzara

Generate all the Dates of a Month in Excel

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:

Generate all the Dates of a Month user input

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.  

Syntax of this function is

Generate all the Dates of a Month EOMONTH  function

EOMONTH here gives the last date of the current month (note the second argument is 0)

Generate all the Dates of a Month last date of current month

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:

Generate all the Dates of a Month SEQUENCE function

In our case the formula to generate all the dates of a month would be

This returns the sequential dates from cell I6 for the month of Jan-24 in a single column as below

Generate all the Dates of a Month

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.

Leave a Reply

Your email address will not be published. Required fields are marked *