To get the same date next month from a given date, you can use the EDATE function. In the worksheet shown, the formula in cell C5 is:


As the formula is copied down, it returns the same date in the next month after the date in column B.

Generic formula



The EDATE function returns a date on the same day of the month, n months in the past or future. You can use EDATE to calculate expiration dates, maturity dates, and other due dates. When 1 is given for months, EDATE returns the same date in the next month. Notice that EDATE automatically handles end-of-month dates properly, adjusting the day when needed to return a valid date.

Same date in the previous month

To get the same date in the previous month, use -1 for months:

=EDATE(date,-1) // prior month


Dave Bruns Profile Picture

AuthorMicrosoft Most Valuable Professional Award

Dave Bruns

Hi - I'm Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.