If you work with dates in Microsoft Excel, EDATE function can really help you particularly for performing date calculations. Such as adding or subtracting a specific number of months to a given date. Here we will look into the details. Including syntax to practical examples.
Table of Contents
Introduction: edate function in excel
The EDATE function is basically for adding or subtracting a specified number of months from a date. This function is particularly useful for calculating due dates. Such as project timelines and other date-related calculations.
The syntax for the EDATE function is below and you can copy paste it.
edate formula in excel
=EDATE(start_date, months)
Here, start_date is the date to add or subtract months. And months is the number of months you want to add or subtract. The function returns a serial number and this one represents the resulting date.
Using excel function edate
If you wanna use the EDATE function, you can follow below steps for application.
- You first select the cell to display the resulting date.
- Then you can type the EDATE function into the formula bar.
- In between the parentheses, you will enter the start_date followed by a comma.
- Now, you can enter the number of months you want to add or subtract. And you should close it with a parenthesis.
- Voila, you just Press Enter.
Here’s an example for edate in excel
In case you have a due date of May 31, 2023 and you want to calculate the due date for three months later. So you can use function to calculate the new due date like below.
=EDATE(“5/31/2023”,3)
The function returns a serial number of 44552; hence the date will be August 31, 2023.
edate function examples: excel edate
Now we will look at some practical examples of how you can use it in Excel.
Example 1: Calculating project end dates
In case you are managing a project that will take 12 months to complete and you want to calculate the end date of the project.
=EDATE(“1/1/2023”,12)
Here the result is serial number of 45477. So it is January 1, 2024 and the end date of the project.
Example 2: Calculating invoice due dates
If you have ab invoice that is due 30 days after the invoice date, you may want to calculate the due date. You can use our function here again.
=EDATE(“4/1/2023”,1)
We get the result of 44417 as formula result which represents May 1, 2023.
Example 3: Calculating retirement dates
So if we suppose you want to calculate the date you will reach retirement age. If retirement age is 65 and your birthdate is 1/1/1960, you can use formula like below.
=EDATE(“1/1/1960”,65*12)
The function returns a serial number of 44247. And this one represents January 1, 2025. This is the retirement date.
Additional Tips on How to Use EDATE Function in Excel?
- You should use cell references for start_date and months arguments. So, Instead of typing in the date and number of months directly, you should use cell references. Hence, you can update the date or number of months in the future.
- You can combine the function with other Excel functions. Such as SUM or AVERAGE if you wanna perform more complex calculations involving dates.
- If you want to calculate the number of months between today’s date and a future date, you can use the TODAY function as the start_date argument in formula.
- You can use conditional formatting to highlight due date. If you are using this function to calculate due dates for tasks or projects, you can use conditional formatting to highlight dates. So that, you can see the ones approaching or overdue.
- When using this function to calculate dates for a certain number of months in the future or past, you should check leap years and adjust your calculations.
FAQs: EDATE function explained
What is the EDATE function used for in Excel?
This basic formula specifically adds or subtracts a number of months from a date.
Can edate examples subtract months from a date?
Yes, the EDATE function can subtract months from a date. If you wanna do this, you shoud enter a negative value for the month’s argument.
How can I format the serial number returned by the function into a readable date format?
You can use the DATE function in Excel to format the serial number returned by this function into a readable date format. For example, if the serial number is in cell A1, you can use the following formula to format it into a readable date:
=DATE(YEAR(A1),MONTH(A1),DAY(A1))
How to use it with dates in different formats?
Yes, you can use it with dates in different formats. But Excel should recognize the date as valid. However, it is better to use mm/dd/yyyy for consistency.
How to use it to calculate the months between two dates?
To calculate the number of months between two dates, you can subtract the earlier date from the later date. You should divide the result by 30 (assuming 30 days per month). And after that you round down to the nearest whole number. For example:
=ROUNDDOWN((B1-A1)/30,0)
Here, A1 and B1 are the dates you want to calculate the number of months between.
Conclusion:
What is the EDATE Function in Excel?
So this one function is ideal for performing date calculations. Because with this formula, you can easily add or subtract number of months from a date. It is a valuable tool for calculating due dates. As well as project timelines, retirement dates and other date-related calculations.
By following the steps and examples here we showed, you should now understand how to use it on in great detail.
A dedicated Career Coach, Agile Trainer and certified Senior Portfolio and Project Management Professional and writer holding a bachelor’s degree in Structural Engineering and over 20 years of professional experience in Professional Development / Career Coaching, Portfolio/Program/Project Management, Construction Management, and Business Development. She is the Content Manager of ProjectCubicle.
