How to Create Dates for 15th and Last Day of Month in Excel

---

Often you may want to create a list of dates in Excel that contain the 15th day of each month and the last day of each month.

For example, you might want to create the following list of dates:

Fortunately this is easy to do in Excel and the following example shows how to do so.

Example: Create Dates for 15th and Last Day of Month in Excel

First, manually enter the date you’d like to start with.

For example, we’ll type in 1/15/2023 into cell A2:

Next, we’ll type the following formula into cell A3:

=IF(DAY(A2)<16,EOMONTH(A2,0),EOMONTH(A2,0)+15)

We’ll then click and drag this formula down to as many cells as we’d like in column A:

The result is a list of dates that only includes the 15th and the last day of the month for each month from January through June of 2023.

How This Formula Works

Recall the formula that we used to generate dates for the 15th and last day of each month:

=IF(DAY(A2)<16,EOMONTH(A2,0),EOMONTH(A2,0)+15)

Here is how this formula works:

If the day is less than 16, then we use the EOMONTH function to specify that the date in the current cell should be equal to the last day of the month.

If the day is not less than 16, then make the date in the current equal to the last day of the month in the previous cell plus 15 more days.

Then end result is that we create alternating cells with dates that are either the 15th of the month or the last day of the month.

Note: You can find the complete documentation for the EOMONTH function in Excel .