How to List All Dates in a Month Using Excel Formula

Sometimes, you may want to list all the dates in a specific month in Excel, such as January 2024. This can be useful for creating calendars, schedules, reports, or other purposes. In this article, we will show you how to use a simple formula to return all the dates in a given month, and how to format the results as dates. We will also provide some alternative methods and tips for working with dates in Excel.

To return all the dates in a specific month, we need to use the Excel DATE function, which takes three arguments: year, month, and day. The DATE function returns a serial number that represents a date, which can be formatted as a date value in Excel.

For example, the formula =DATE(2024,1,1) returns the serial number 44245, which corresponds to January 1, 2024. If we format the cell as a date, we will see 1/1/2024 displayed.

To return all the dates in a specific month, we need to use the DATE function in an array formula, which is a formula that can perform multiple calculations and return multiple results. An array formula is entered by pressing Ctrl + Shift + Enter, instead of just Enter.

The basic syntax of the array formula to return all the dates in a specific month is:

=DATE(year,month,ROW(INDIRECT("1:31")))

Where year and month are the values of the year and month that you want to list the dates for. The ROW and INDIRECT functions are used to generate an array of numbers from 1 to 31, which represent the possible days of the month. The DATE function then returns an array of serial numbers that correspond to the dates in the specified month.

For example, the formula =DATE(2024,1,ROW(INDIRECT("1:31"))) returns an array of 31 serial numbers, from 44245 to 44275, which correspond to the dates from January 1, 2024 to January 31, 2024.

Procedures

To use the array formula to return all the dates in a specific month, follow these steps:

1. Enter the year and month that you want to list the dates for in two separate cells, such as A1 and B1.
2. Select a range of cells where you want to display the dates, such as C1:C31. Make sure the range has the same number of rows as the maximum number of days in a month, which is 31.
3. Type the formula =DATE(A1,B1,ROW(INDIRECT("1:31"))) in the formula bar, but do not press Enter yet.
4. Press Ctrl + Shift + Enter to enter the formula as an array formula. You should see curly braces {} around the formula in the formula bar, indicating that it is an array formula.
5. Format the cells as dates by clicking Home > Number Format > Short Date or Long Date, depending on your preference.
6. You should see the dates in the specified month displayed in the selected range.

Example

Suppose we want to list all the dates in February 2024 in Excel. We can use the following steps:

1. Enter 2024 in cell A1 and 2 in cell B1, as shown below:

2. Select the range C1:C31, and type the formula =DATE(A1,B1,ROW(INDIRECT("1:31"))) in the formula bar, but do not press Enter yet.
3. Press Ctrl + Shift + Enter to enter the formula as an array formula. You should see curly braces {} around the formula in the formula bar, indicating that it is an array formula.
4. Format the cells as dates by clicking Home > Number Format > Short Date or Long Date, depending on your preference.
5. You should see the dates in February 2024 displayed in the range C1:C29, as shown below:

Note that the cells C30 and C31 display the #NUM! error, because there are no dates in February 2024 after the 29th. This is normal and expected, as the formula returns an array of 31 values, regardless of the actual number of days in the month.

Alternative Methods and Tips

There are some alternative methods and tips for returning all the dates in a specific month in Excel, such as:

• Using the EOMONTH function to get the last day of the month, and then subtracting the number of days from it to get the first day of the month. For example, the formula =EOMONTH(DATE(2024,2,1),0)-ROW(INDIRECT("1:31"))+1 returns the same result as the previous formula, but in reverse order.
• Using the SEQUENCE function to generate an array of numbers from 1 to 31, instead of the ROW and INDIRECT functions. The SEQUENCE function is available in Excel 365 and Excel 2019. For example, the formula =DATE(2024,2,SEQUENCE(31)) returns the same result as the previous formula, but without the need to enter it as an array formula.
• Using the TEXT function to format the dates as text values, instead of formatting the cells as dates. For example, the formula =TEXT(DATE(2024,2,ROW(INDIRECT("1:31"))),"mm/dd/yyyy") returns the same result as the previous formula, but as text values. This can be useful if you want to use the dates in other formulas or functions that require text inputs, such as CONCATENATE or VLOOKUP.
• Using the DATEVALUE function to convert the text values back to date values, if needed. For example, the formula =DATEVALUE(TEXT(DATE(2024,2,ROW(INDIRECT("1:31"))),"mm/dd/yyyy")) returns the same result as the previous formula, but as date values. This can be useful if you want to perform calculations or comparisons with the dates, such as adding or subtracting days, or finding the minimum or maximum date.