EDATE Function
It is useful for employers in calculating future deadlines, maturity, and expiration dates.
What is the EDATE Function?
Edate is an Excel function, falling in the Date and Time Functions category, 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 that graduated with a bachelor's in finance, this tool is resourceful for future financial advisors.
Key Takeaways
- Once you’ve imputed your beginning point and the number of months in the formula, you’ll return a serial number representing a day before or after the starting point.
- Positive values represent a day in the future, while negative values represent a day in the past. Likewise, positive numbers add months, while negative numbers subtract months from the starting point.
- It works by using =Today() as your starting point. Then, it shows you how many months there are, in the future or past, from today.
- You cannot get a different day by putting a decimal unit in the function. So, for example, if you put 1.5 for months and use 1/1/2022 as your starting point, you’ll get back 2/1/2022 as it doesn’t calculate additional days; it only returns the same day but in a different month.
- It is referenced as a Date & Time function.
- The function is useful for employers in finding maturity, due, and expiration dates.
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 5 months from that point in time, and 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 an answer of 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.
or Want to Sign up with your social account?