Excel EDATE Function to add month

What is Excel EDATE Function?

Excel EDATE Function is to add (or subtract) specified months to a date. For example, adding 2 months to Jan 14, 2015 would be Mar 14, 2015.

Syntax of EDATE

EDATE(start_date, months)

start_date A date to add months to
months Positive integer to indicate to add month to start_date, negative to indicate subtraction

By default, a Cell Format is “General” which does not display a date as date format (such as mm/dd/yyyy), instead date is displayed as numerical value (date serial). Go to Format Cell and choose the desired Date format in order for the Function to display date.

Example of EDATE

Assume that Cell A1 contains a date 1/1/2015, Cell A2 contains a date 31/1/2015

Formula Result Explanation
=EDATE(A1,2) 1/3/2015 Add two months to 1/1/2015
=EDATE(A1,-2) 1/11/2014 Subtract two months from 1/1/2015
=EDATE(A2,1) 28/2/2015 Since Feb does not have 31 days, the Function returns the last date of Feb
=EDATE(A1,12*3) 1/1/2018 Add three years to 1/1/2015
=EDATE(Date(2015,1,1),2) 1/3/2015 Instead of selecting a Range, use Date Function to convert (2015,1,1) to date serial in the start_date parameter

Alternative of EDATE

Assume that we add 5 years to 2015 Jul 1 (A1)

Formula Result
=DATE(YEAR(A1)+5,MONTH(A1),DAY(A1)) 01/07/2020

Assume that we add 5 months to 2015 Jul 1 (A1)

Formula Result
=DATE(YEAR(A1),MONTH(A1)+5,DAY(A1)) 1/12/2015

To see how to add day, hour, minute and second, read the below article.

Excel Function add year month day hour minute second to date

Outbound References

https://support.office.com/en-us/article/EDATE-function-857e149f-f013-4581-8d35-4f96b376c9ed?CorrelationId=e6058146-7872-4c54-908f-a0a9e32090fa&ui=en-US&rs=en-US&ad=US

 

Wyman W
Wyman is a Human Resources professional based in Hong Kong, specialized in business analysis, project management, data transformation with Access and Excel.

He is also a:
- Microsoft Most Valuable Professional (Excel)
- Microsoft Community Contributor
- Microsoft Office Specialist in Access / Excel
- Microsoft Specialist in MS Project
- Microsoft Technical Associate
- Microsoft Certified Professional
- IBM SPSS Specialist

Leave a Reply

Your email address will not be published. Required fields are marked *