EDATE Function
An Excel Date and Time Function that returns a specified day in the number of months before or after a specific starting point.
What is the EDATE Function?
The EDATE function is an Excel Date and Time Function that returns a specified day in the number of months before or after a specific starting point. It is useful for employers in calculating future deadlines, maturity, and expiration dates.
EDATE adds a given number of months from a starting point and returns the result as the same day of the month, x months from now or in the past. In addition, it returns the date’s serial number, showing how many months there were before or after the starting point.
EDATE is helpful to someone like me who is finding a job in the work field and wants to apply their financial skillset in the real world. Additionally, as someone who graduated with a bachelor's in finance, this tool is resourceful for future financial advisors.
Key Takeaways
- The EDATE function in Excel calculates a date that is a specified number of months before or after another date.
- EDATE helps calculate maturity dates, expiration dates, and other dates that are a fixed number of months away from a starting date.
- The function is commonly used in financial and accounting scenarios to calculate future or past dates based on a reference date. The function is helpful for employers in finding maturity, due, and expiration dates.
- The syntax for the EDATE function is =EDATE(start_date, months). start_date (required): The start date from which to calculate the new date. Months (required): The number of months before (months < 0) or after (months > 0) the start_date.
EDATE Function Formula
When using Edate, you learn how to find dates in the past and future. By inserting a positive integer for months, you’ll get a day in the future, while negative integers return a day in the past.
Where
- start_date = the month and day that you want to start with
- months = the number of months before or after the beginning point.
- Positive integers calculate a future day
- Negative integers calculate dates in the past.
We will use this formula in greater context by providing some examples to help you better understand how it works.
Note
Edate calculates the number of months from one starting point and returns the future or past date as the answer.
How to use the EDATE Function in Excel?
This is pretty easy to use. All you have to do is insert a beginning date and the number of months in the formula, and you should get the same day but in a different month as your answer.
Let’s use the date in C2 (April 5th, 2016) as our beginning point for reference. You put that cell in the start_date category. Suppose you want to find the day 3 months from now. In the month's category, you put the number 3, and you should get July 5th, 2016, as your answer.
We’ll look at this problem in greater detail by using more examples. Below is a screenshot of some random examples calculated using this function.
In the first example, we wanted to determine the future date from our starting point. So, we used March 4th, 2019, as my starting point, and we wanted to find out what the day is five months from that point in time. We got August 4th, 2019, as our answer in cell C4.
Next, we wanted to find a date in the past. Using May 24th, 2020, as the beginning and -4 in months to calculate a day in the past, we got January 24th, 2020, in cell C5. This is because negative months indicate a day in the past.
Then, we wanted to find out if this formula works with decimals. Putting June 21st, 2021, as our starting point and 0.75 for the number of months gave me June 21st, 2021, in cell C6. As you can see, it does not work with numbers that have decimals in them.
In row 7, we wanted to find a date in the next year. Using April 17th, 2022, as an example and 15 months, we got July 17th, 2023, as our answer in cell C7.
Finally, we wanted to find a future date from today. Using =TODAY() as our starting point, which gives us today’s date of February 7th, 2023, and 6 for the number of months, we got August 7th, 2023 as my answer in cell C8.
EDATE vs. YEARFRAC Function
So now you have a good understanding of what Edate does, you’re probably asking if there is a way to calculate the difference between two days, and the answer is yes, by using the YEARFRAC function.
The YEARFRAC function aims to return a decimal value showing the difference between two days. It can be used in everything from finding the remaining years between deadlines to calculating an age with a birthdate.
Where,
- start_date - the starting point
- end_date - the stopping point
- basis [optional] - the type of day count basis that takes values from 0 to 4.
We will understand this syntax in greater detail by using some examples. Below is a screenshot showing the two tables.
In the first example, we wanted to find the difference between two days five months apart. Using the YEARFRAC function, we got an answer of 0.42 remaining years because there are 12 months in a year, and 5 divided by 12 is approximately 0.42.
Next, we wanted to find the difference between a day in the past as our beginning and a later date in the future as our ending, and we got an answer of 0.3 remaining years as it only shows the difference between the two dates.
Then, we wanted to see what would happen if we used the same day as both the starting and ending point (June 21st, 2021), and we got an answer of 0 because there is no difference between the two dates.
Then, we wanted to determine how long 15 months were in the remaining years. We used the same function to calculate the rest of the problems. We got an answer of 1.25 remaining years.
Finally, we wanted to use a random day in the past (January 18th, 2000) and use =TODAY() as our endpoint to find the difference between the two, and we got an answer of approximately 23.05 years.
Free Resources
To continue learning and advancing your career, check out these additional helpful WSO resources:
or Want to Sign up with your social account?