How to Use Excel EDATE Function

Last updated on May 14th, 2018

Excel provides a wide variety of functions to handle date calculations. We have already looked at some of these functions in previous tutorials. The EDATE function is utilized, when one wants to add or subtract a certain number of months from a specific date and find out what the new date will be after the addition or subtraction of the months. In this article, we will see the use of Excel EDATE Function.

EDATE Function in Excel

Syntax

The syntax of the EDATE Function is:

where:

  • start_date is the date that you want to add the months to,
  • and months is the number of months you’d like to add or subtract from the start_date, months can be a positive or negative value. If you use a negative value as the months value, then an earlier date will be delivered rather than a later date as in the case of a positive value.
Learn Excel Online: Top Excel Courses Online

Let’s Explain How to Use EDATE with Excel

So, let’s get started with a simple example to illustrate how to use the EDATE Function.

A hypothetical Non-governmental Organization (NGO), has five different main donors that donate but usually according to different funding schedules. So the NGO would like to see, in order to plan for the year (based on previous monthly data) and allocate money to their projects, how long after the initial date of contacting the donor, they can expect the donation. As mentioned they have the number of months, the donor usually takes to make a donation to the NGO, gleaned from previous years data.

The source data is shown below.

How to use edate function in Excel

1) So in order to see when the NGO can expect the donation in Cell D7, we enter the following formula:

=EDATE(B7, C7)

Edate Excel Function

2) Upon pressing CTRL-ENTER we have the value returned as a serial number 42826, remember Excel stores dates as serial numbers, counting the number of days since 1 January 1900.

3) We then drag the formula down as shown below.

4) In order to see the Expected Date of Donation in proper date format, we select the range D7: D11 as shown below.

5) We right-click and choose Format Cells.

6) In the Format Cells dialog box, under the Number tab, select Date and choose the date format as shown below.

7) Click Ok and the values in range D7: D11 are now shown in the long date format we selected.

And there you have it.

Download Zone

EDATE-Excel-Function

Conclusion

The EDATE Function is utilized in order to add a number of months to a date and return the new date, in serial number format. In order to see the date in date format, you will have to format the cell range of interest.

Please feel free to comment and tell us if you often use the EDATE Function in your spreadsheets.

Useful DATE/Time Function related Links from ExcelDemy

Date & Time in Excel – How to Enter Them in Worksheet Cells Effectively?

How to use the TODAY() and NOW() Functions in Excel

How to Use the Excel DAYS Function with a Practical Example

How to Use the DATEDIF Function in Excel

Calculate The Number of Days/Weeks/Months/Years between Two Dates in Excel

Review Section: Test your Understanding

1) What is the EDATE Function used for?

2) What is the syntax of the EDATE Function?

3) Can the month entered be negative as well?

4) What is the value returned in, in terms of what format is the value returned in?

5) Calculate the date, using the EDATE Function, three months after 29 June 2017.

Read More…

Excel If Function with Multiple Conditions (Nested IF)

Practice Excel Indirect Function with 3 Case Studies

Taryn is a Microsoft Certified Professional, who has used Office Applications such as Excel and Access extensively, in her interdisciplinary academic career and work experience. She has a background in biochemistry, Geographical Information Systems (GIS) and biofuels. She enjoys showcasing the functionality of Excel in various disciplines.

She has over ten years of experience using Excel and Access to create advanced integrated solutions.

2 Comments
  1. Reply mhier227@gmail.com'
    Mark Hier January 13, 2018 at 9:34 PM

    that works fantastically. can you help me with a problem please?

    this is the formula that i am currently using: =datedif(b1,b2,”Y”)& ” years, “&datedif(b1,b2,”YM”)& ” months, “&datedif(b1,b2,”MD”)& ” days. ” and that shows me the years, months and days. how do you work out the years, months, weeks and days please?

    many thanks

    Mark

  2. Reply
    Kawser January 14, 2018 at 1:22 PM

    Hi Mark,
    Use the following formula instead of the formula you’re using.
    =DATEDIF($B$1,$B$2,”Y”)& ” years, “&DATEDIF($B$1,$B$2,”YM”)& ” months, “&ROUNDDOWN(DATEDIF($B$1,$B$2,”MD”)/7,0)&” Weeks, and “&DATEDIF($B$1,$B$2,”MD”)-ROUNDDOWN(DATEDIF($B$1,$B$2,”MD”)/7,0)*7&” Days.”
    Regards
    Kawser

    Leave a reply