We explored the NOW function and DATEDIF in other blog articles. This time we’ll look at EDATE.
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.
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.
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.
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.
What’s Next?
Learning to use functions is a great way to become more productive in Excel.
If you’ve enjoyed reading about the EDATE function, you might want to check out XLOOKUP vs VLOOKUP: Excel Trainer Explains Key Differences. XLOOKUP is a game changer for anyone who needs to combine data from multiple sources.
See you next time!
