Go Back

Excel EDATE Function

Generic Formula

=EDATE (start_date, months)

Explanation

Excel EDATE function can be used to add or subtract a specific number of months to a date. This excel formula helps in the calculation of due dates, expiry dates, and maturity dates. To get a future date we use positive values of months while the negative values of months are used to get the dates in the past.

What is the purpose of the Excel EDATE function?

The purpose of the Excel EDATE function is to get the same date in the past and future months.

What value will it return?

It returns us a serial number that represents a date.

Example

In the below example, the formula used in E5 is

=EDATE (C5,D5)

We have mentioned start date in C5 and the number of months to count in D5. We have got the result in E5 after adding or subtracting the number of months from the start date.

Figure 1 – Example of the use of Excel EDATE formula to get the desired dates in the past or future.

We have used positive values of months in D5, D7 and D8 to get the future dates, whereas, negatives values in D6, D9, and D10 for the months in the past.

Arguments

Start_date: Representation of the start date in Excel’s serial number format.  (Representing dates from C5 to C10 in our case).

Months: The number of months to calculate before or after start_date. (In our example, it is ranging from D5 to D10).

Excel EDATE function usage notes

EDATE is used to get the same date in the past or future months. For future dates, use a positive value of month such as (6), and for the past dates, use the negative value of the month as (-6).

EDATE is a clever function that rolls ‘end of month’ dates, in the normal and Leap years, and returns the dates after calculating them.

 

Did this post not answer your question? Get a solution from connecting with the expert.

Another blog reader asked this question today on Excelchat:
Here are some problems that our users have asked and received explanations on

why is this not working : +SUMPRODUCT(--(A2:A9=M1), --(B2:B9>=EDATE(TODAY(),-M2)),D2:D9,C2:C9)/SUMIFS(D2:D9,A2:A9,M1,B2:B9,>EDATE(TODAY(),-M2))
Solved by Z. A. in 24 mins
Date highlight conditional formatting not recognizing cells with EDATE formula adding more than 5 months to date. For example: Conditional Formatting: Current month = Highlight Green Start date in cell A1: 3/1, next cell has formula =EDATE(A1,3) Formula populates new date to 6/1 and CELL TURNS GREEN Start date in cell A1: 12/1, next cell has formula =EDATE(A1,6) Formula populates new date to 6/1 and CELL DOES NOT TURN GREEN Start date in cell A1: 9/1, next cell has formula =EDATE(A1,9) Formula populates new date to 6/1 and CELL DOES NOT TURN GREEN
Solved by A. E. in 19 mins
hey, I am trying to integrate the EDATE function with an IF statement. On an income sheet, I want to be able to automatically populate the estimated figures for a job, using the variables Net Fee, Current Date, Comm Start Date and Contract Length. So for each month, currently there's a formula that says =IF(MONTH(Current Date)=MONTH(EDATE(Comm Start Date, Contract Length)), Net Fee, 0) But this doesn't then continue displaying the Net Fee for the rest of months. What I would like it to do is: =IF(MONTH(Current Date)=MONTH(EDATE(Comm Start Date, [Any number between 0 and Contract Length])), Net Fee, 0) How is this possible?
Solved by E. Q. in 29 mins

Leave a Comment

avatar