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.
Table of Contents
EDATE Function in Excel
The syntax of the EDATE Function is:
- 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.
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.
1) So in order to see when the NGO can expect the donation in Cell D7, we enter the following formula:
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.
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.
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.