List all dates in a month

In this example, we'll use SEQUENCE to generate all dates in a given month. Creating a complete list of dates for a specific month is a common Excel task with many practical applications, from building project timelines and work schedules to generating calendar views and tracking daily data. The input is any date within the target month (it doesn't matter which specific day), and the output is a dynamic list that automatically adjusts when you change the input date. The technique works because Excel stores dates as serial numbers, allowing SEQUENCE to count through them just like any other numeric sequence. Although the core of the solution is SEQUENCE, it's also interesting how we use the DAY and EOMONTH functions to calculate the inputs to SEQUENCE. The EOMONTH function is particularly useful and comes up in all kinds of other formulas. The DAY function (together with EOMONTH) is a clever way to get the total days in a month.

Table of Contents

• The SEQUENCE function
• SEQUENCE with dates
• SEQUENCE to list all dates in a month
• LET version of the formula
• All dates between two dates
• Approach for older versions of Excel
• Useful links

The SEQUENCE function

The SEQUENCE function can generate numeric sequences using a generic syntax like this:

=SEQUENCE(rows,[columns],[start],[step])

Rows is the number of rows to return, columns is the number of columns to return, start is the starting value, and step is the increment to use between values. The arguments columns, step, and start all default to 1. For example, we can ask for the numbers 1-10 like this:

=SEQUENCE(10) // returns {1;2;3;4;5;6;7;8;9;10}

To get the numbers 51-60, we can set start to 50:

=SEQUENCE(10,,50) // returns {50;51;52;53;54;55;56;57;58;59}

In both formulas above, SEQUENCE generates an array of numbers that will spill into 10 rows. 

SEQUENCE with dates

Because dates are just numbers in Excel, we can easily configure SEQUENCE to output dates. For example, to output the first 5 days in May 2025, we could use a formula like this:

=SEQUENCE(5,,"1-May-2025") // returns {45778;45779;45780;45781;45782}

The result will be an array of serial numbers, as shown above. In this array, the number 45778 corresponds to the date 1-May-2025 in Excel's date system. To display these numbers as dates, apply number formatting.

SEQUENCE to list all dates in a month

In the worksheet shown, we want to generate a list of all dates in a given month, where the month is input as a date in cell B5. To use SEQUENCE for this task, we need to calculate two input values based on the date in B5: (1) the number of days in the month and (2) the first day of the month. To get the number of days in the month, we can use the DAY function like this:

=DAY(EOMONTH(B5,0))

In this snippet, we use EOMONTH to get the last day of the current month (note the offset is zero), then we use the DAY function to get the number of days in the month. For example, with a date like 15-May-2025 in B5, EOMONTH returns the date 31-May-2025, and DAY returns 31, which is equal to the number of days in the month. To get the first day of the month, we can use the EOMONTH function like this:

=EOMONTH(B5,-1)+1 // get first day in month

Here, we use EOMONTH to travel back to the last day of the "previous" month and then add 1 to move forward one day to land on the first day of the "current" month, relative to the date in B5. The final formula in cell D5 looks like this:

=SEQUENCE(DAY(EOMONTH(B5,0)),,EOMONTH(B5,-1)+1)

The inputs provided to SEQUENCE are as follows:

• rows - DAY(EOMONTH(B5,0)) // days in month
• columns - omitted, defaults to 1
• start - EOMONTH(B5,-1)+1 // first of month
• step - omitted, defaults to 1

With the above configuration, SEQUENCE returns an array of 31 serial numbers that correspond to all dates in May 2025:

{45778;45779;45780;45781;45782;45783;45784;45785;45786;45787;45788;45789;45790;45791;45792;45793;45794;45795;45796;45797;45798;45799;45800;45801;45802;45803;45804;45805;45806;45807;45808}

You must apply a number format for dates to display these serial numbers as dates.

The output is fully dynamic. If the date in B5 is changed to 9-Jun-2025, the formula will list the 30 dates in June 2025. Notice that the actual date given to SEQUENCE doesn't matter because the formula automatically calculates the first day of the month (with EOMONTH, as explained above) for the start value in SEQUENCE. 

LET version of the formula

We can use the LET function to clean up the formula above somewhat like this:

=LET(
  date,B5,
  first,EOMONTH(date,-1)+1,
  days,DAY(EOMONTH(date,0)),
  SEQUENCE(days,,first)
)

This is an excellent example of how the LET function creates cleaner code and results in a formula that is easier to read and debug.

Tip: To see the entire formula above in the formula bar, use the shortcut Control + U.

All dates between two dates

The generic formula to create a list of all dates between two dates looks like this:

=SEQUENCE(end-start+1,1,start)

This can be adapted to generate a list of all dates in a month like this:

=LET(
  date,B5,
  first,EOMONTH(date,-1)+1,
  last,EOMONTH(date,0),
  SEQUENCE(last-first+1,,first)
)

The results will be the same, but I think the first formula above is slightly easier to understand and configure.

Approach for older versions of Excel

In older versions of Excel, we don't have the SEQUENCE function, so we need to take a different approach. There are many options for this kind of problem, but I think the approach shown below works pretty well:

There are two different formulas in the worksheet. The first formula in cell D5 gets the first-of-month from the date in cell B5 like this:

=EOMONTH(B5,-1)+1

The second formula, entered in cell D6 and copied down manually until it returns nothing, looks like this:

=IFERROR(IF(D5+1>EOMONTH($D$5,0),"",D5+1),"")

Working from the inside out, the core formula increments dates with the IF function like this:

IF(D5+1>EOMONTH($D$5,0),"",D5+1)

The formula first adds 1 to the value in D5, then checks if the resulting date is greater than the last day of the month, calculated with EOMONTH($D$5,0). If so, IF returns an empty string (""), which looks like a blank cell. If not, the result is D5+1, which adds one day to the previous date. As the formula is copied down, it generates all dates in the given month and then begins to output "blank" cells. To handle the #VALUE! error that arises after the first blank cell, the core formula above is wrapped in the IFERROR function:

=IFERROR(formula,"")

IFERROR catches the value error and returns an empty string ("") when necessary. The result is that you can copy the formula well past the end of the month and not see any errors.

Useful links

• How to use the SEQUENCE function - overview
• The SEQUENCE function - 3 min video
• SEQUENCE of dates - 3 min video
• Dynamic Array Formulas - Video training