Categories
Microsoft Excel

Use EDATE to Add Months to a Date in Excel

You can use the EDATE function to add months to a date in Excel. Do you need to subtract months from a date? Don’t worry… EDATE will do that too!

The EDATE function returns a serial number that represents a date offset by the number of months before or after a date you specify.

I want to emphasize that EDATE returns a serial number.

Just remember to format any cell containing the EDATE function to display its result as a date – otherwise, you’ll see some weird number that’s not all that helpful!

Participants in our Microsoft Excel training are always baffled by Excel’s date serial numbers. The serial numbers represent the number of days since January 1, 1900. January 1, 1900 would is counted as 1. August 5, 2022 is 44,778.

EDATE Function Syntax

The EDATE function has two arguments. Both are required.

=EDATE(start_date, months)

start_date is a date. It can be a cell reference or the result of another formula.

months represents the number of months you’d like to add or subtract from start_date. Use positive numbers to add months, negative numbers to subtract.

How to Add Months to a Date with the EDATE Function

Let’s look at an example or two.

In the spreadsheet below, I used the EDATE formula to add months to a date in Excel. In cell C2, I typed =EDATE(B2,1) and pressed Enter.

Using the EDATE function to add months to a date

The start date in cell B2 is 8/5/2022, the end date in cell C2 is 9/5/2022.

I formatted all of the cells in column C as dates. The cells in column D have the general format applied to display dates as serial numbers.

Use EDATE to Subtract Months from a Date

To subtract months from a date, simply use a negative number.

In the spreadsheet below, I used the EDATE function to subtract months from a date. In cell C2, I typed =EDATE(B2,-1) and pressed Enter.

Using EDATE to subtract months

Once again, the date format applied to column C to render the serial numbers returned by the EDATE function in a user-friendly manor.

Use the EDATE Function to Add Years to a Date

If you want to add years to a date, multiply the number of months by 12.

In the spreadsheet below, I used the EDATE formula to add years to a date in Excel. In cell C2, I typed =EDATE(B2,1*12) and pressed Enter.

Add years with EDATE

If you want to subtract years from a date in Excel, use a negative number.

If I type the formula =EDATE(B2,-1*12) in cell C2, the result will be 8/5/2021.

By Michael Belfry

Working as a full-time training consultant, Michael provides Microsoft Office courses to government and private sector clients across Canada.