EDATE is a date and time function in excel which adds a given number of months into a date and gives us a date in a numerical format of date, the arguments this function takes is date and integers, date as the start date to begin with and number of months are integers to add it to the given start date, the output returned by this function is also a date value, the method to use this function is =Edate( Start Date, Months).
EDATE Function in Excel
EDATE Function is a built-in function in MS Excel. EDATE Function falls under the category of DATE and TIME functions in Excel. EDATE in excel is used to get a date on the same day of the month, x months in the past or future. EDATE function returns the serial number of the date that is the indicated number of months before or after the given start date. The EDATE function in excel is used to calculate expiration dates, maturity dates, and other due dates. To get a date in the future, use a positive value for months and a negative value for dates in the past.
EDATE Formula in Excel
Below is the EDATE Formula in Excel.
EDATE in Excel has two arguments, out of which both are required. Where,
- Start_date =A date that represents the start date in a valid Excel serial number format.
- months = The number of months before or after start_date.
How to Use EDATE Function in Excel?
The EDATE function in excel is a Worksheet (WS) function. As a WS function, EDATE in excel can be entered as a part of the formula in a cell of a worksheet. Refer to a couple of examples given below to know more.
EDATE Function in Excel Worksheet
Let’s look at the EDATE Function examples given below. Each example covers a different use case implemented using EDATE Excel Function.
EDATE in Excel Example #1 – Get the same date 3 months later
In the above example, EDATE(B2,3) returns a date that is three months later than the date mentioned in cell B2. Cell B2 has date 23rd Sept 2018. So, the date after 3 months is 23rd Dec 2018, and the same is displayed as a result in cell C2 wherein the EDATE Function is applied.
EDATE in Excel Example #2 – Add months to date
In the above example, cell B5to B7 contains the source date.
Cell C5 to C7 contains the number of months to be added to the dates from the B column.
Cell D5 to D7 contains the result date.
The EDATE function used is EDATE (B5,C5), EDATE (B6,C6), and EDATE(B7,C7) respectively.
Row 5, the date is 1st Jan 2018, and 12 months are added to the date resulting in the date 1st Jan 2019. Row 6, the date is 10th Jan, from which a year, i.e., 12 months, are deducted, resulting in the date as 10th Jan 2017. Row 7, the source date is 23rd Sept 2018 to which 24 months, i.e., 2 years are added, and the result date is 23rd Sept 2020.
EDATE in Excel Example #3 – Calculate the retirement date from the birth date
In the above example, the birth date is given. From using the birth date (Col B) and the retirement age criteria, the retirement date is calculated (Col C). Years Left is another column (Col D) wherein the number of years of service left is calculated using the YEARFRAC Excel functionYEARFRAC Excel FunctionYEARFRAC Excel is a built-in Excel function that is used to get the year difference between two date infractions. This function returns the difference between two dates infractions like 1.5 Years, 1.25 Years, 1.75 Years, etc. So using this function we can find the year difference between two dates accurately..
As shown in the above EDATE function in Excel 1 box, i.e., EDATE(B10,12*58), B10 contains the birth date, which is 21st May 1965.
In the above screenshot, the retirement date calculated is 21st May 2023, which is 58 years after the birth date.
2nd formula is YEARFRAC (TODAY (), C10), i.e., the difference between today’s date and the retirement date, which is 4 years 7 months. Consider the screenshot given below.
The same EDATE function is applied to the remaining two rows, i.e., B11 and C11.
Cell B11 contains the birth date as 5th Sept 1962. The retirement date calculated is 5th Sept 2020, which is 58 years after the birth date
And the years left in cell D11 is calculated to be 1.9, which is 1 year 9 months after today’s date.
EDATE in Excel Example #4– Calculate expiry date
In the above example, cell B14 contains the start date or manufacturing date of the product, cell C14 contains the duration for which the product can be consumed. Cell D14 is the resulting cell, which indicates the expiry date of the product. Here, 3rd April 2018 is the manufacturing date, and the duration is 30 days, which means 1 month. So, the EDATE formula applied is EDATE(B14,1), and the result is 3rd May 2018, which is 1 month after the start date. Cell D14 contains the result.
Things to remember about EDATE in Excel
- The 2nd parameter of the EDATE Excel Function must be a positive number.
- If start_date is an invalid date, EDATE in excel returns the #VALUE! Error value.
- If months are not an integer, the value is truncated to be an integer.
- Microsoft Excel stores date as sequential serial numbers so they can be used in calculations. By default, January 1st, 1900, is serial number 1, and January 1, 2001, serial number is 36892.
EDATE Excel Function Video
This has been a guide to EDATE in Excel. Here we discuss the EDATE Formula in excel and how to use EDATE function in excel along with EDATE excel Function examples and downloadable excel templates. You may also look at these useful functions in excel –