You can use the following formula to create a series of monthly dates in Excel:
=DATE(YEAR(A2),MONTH(A2)+1,DAY(A2))
This formula assumes that the date you’d like to start with is manually entered into cell A2.
You can then click and drag this formula down to as many cells as you’d like in a column to create a series of dates that are exactly one month apart.
The following example shows how to use this formula in practice.
Example: Create Series of Monthly Dates in Excel
Suppose we would like to create a series of monthly dates that ranges from 1/15/2020 to 12/15/2021.
To do so, we can manually type the first date into cell A2:
Next, we can type the following formula into cell A3:
=DATE(YEAR(A2),MONTH(A2)+1,DAY(A2))
This formula will automatically produce the date that is one month after the date in cell A2:
Lastly, we can click and drag this formula down to other cells in column A until we reach 12/15/2021:
The result is a series of monthly dates ranging from 1/15/2020 to 12/15/2021.
How This Formula Works
Recall the formula that we used to create a series of monthly dates:
=DATE(YEAR(A2),MONTH(A2)+1,DAY(A2))
Here is how this formula works:
First, the YEAR function extracts the year from cell A2.
Next, the MONTH function extracts the month from cell A2. We then add one to this month to return the next month.
Next, the DAY function extracts the day from cell A2.
Lastly, we wrap each of these functions with the DATE function to convert the final serial number to a recognizable date.
The end result is that we’re able to return a date in cell A3 that is exactly one month after the date in cell A2.
Additional Resources
The following tutorials explain how to perform other common operations in Excel:
How to Count by Month in Excel
How to Calculate Average by Month in Excel
How to Convert Date to Month and Year Format in Excel
