Hi - Dave here.
Happy Friday!
This week, we look at a relatively simple formula to list all dates in a given month. You can see how it works in the worksheet below, where the formula in cell D5 looks like this:
=SEQUENCE(DAY(EOMONTH(B5,0)),,EOMONTH(B5,-1)+1)
The core of this solution is the SEQUENCE function, which generates the list of dates. What's interesting is how we use the DAY and EOMONTH functions to calculate the required 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.
[
Download the workbook and read the full explanation]
The result from the formula above is a list of the 31 dates that belong to May 2025. Note that the actual date given to SEQUENCE doesn't matter because the formula automatically creates the first day of the month as the start value in SEQUENCE. 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.
I also included a LET version of the formula. This is a nice example of how the LET function creates cleaner code and results in a formula that is easier to read and debug. Download the workbook and try it out yourself.
Note: SEQUENCE requires Excel 2021 or later. However, the workbook includes a workaround formula that will work in older versions of Excel.
Excel formulas
We maintain a list of over 1000 working formulas
here.
If you need more structure, we also offer
video training.
Have a great weekend!
Dave
The Exceljet newsletter is free and sent weekly on Fridays. Each week, I take a detailed look at how to solve a specific problem in Excel. You can sign up on our home page.
