Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, 2013, 2016, 2019, and 2021. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here: The EDATE Function.

The EDATE Function

Written by Allen Wyatt (last updated July 3, 2024)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, and 2021


8

If you have a need to determine a date that is a known number of months in the future or the past, then the EDATE function can be quick and easy. For instance, if you are working with expiration dates for six-month contracts, you can use the following formula:

=EDATE(NOW(),6)

The function takes the first date provided (in this case, using the NOW function) and uses the second parameter to determine the number of months future or past that should be calculated. The date parameter you use should resolve to a date serial number and not be a textual date.

If you use a negative value for the second parameter, then EDATE calculates a date in the past. For instance, if you wanted a date that was three months in the past, then you could use the following:

=EDATE(NOW(),-3)

EDATE returns a date serial number; you may need to format the cell, so it uses a date format that formats the returned value as you want it to appear.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (9392) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, and 2021. You can find a version of this tip for the older menu interface of Excel here: The EDATE Function.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Removing a Directory

Macros allow you to perform all sorts of file-related operations. One such operation allows you to delete a directory. ...

Discover More

Stable Layout on Different Printers

Want your document to print out the same on printers other than your own? This may be an elusive quest, as explained in ...

Discover More

Counting Words the Old Fashioned Way

One way to specify word count is to count characters and divide by five. If you still need this old-fashioned way of ...

Discover More

Professional Development Guidance! Four world-class developers offer start-to-finish guidance for building powerful, robust, and secure applications with Excel. The authors show how to consistently make the right design decisions and make the most of Excel's powerful features. Check out Professional Excel Development today!

More ExcelTips (ribbon)

Disabling #SPILL! Errors

In the newest version of Excel, a change in how formulas are calculated can cause havoc for some "older" formulas. Here ...

Discover More

Understanding the LET Function

The LET function provides an easy way to define and use variables within a formula. This tip shows you how you can start ...

Discover More

Choosing SUBSTITUTE or REPLACE

If you need to modify information in a text string, two common functions you can use are SUBSTITUTE and REPLACE. This tip ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] (all 7 characters, in the sequence shown) in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is seven more than 2?

2025-02-02 11:43:04

J. Woolley

Re. my most recent comment below, if your version of Excel includes REGEXREPLACE, that function takes precedence and My Excel Toolbox's RegExReplace cannot be referenced. Therefore, the original RegExReplace was renamed RegExSubstitute. A legacy version of RegExReplace was retained; it simply calls RegExSubstitute using the same parameters.
See https://sites.google.com/view/MyExcelToolbox/


2024-07-16 05:01:08

Kiwerry

@ J. Woolley: thanks very much, I'll take a look at that.


2024-07-15 17:46:06

J. Woolley

@Kiwerry
My Excel Toolbox includes the following regular expression functions:
    =IsRegEx(Text,Pattern,[IgnoreCase],[Multiline])
    =RegExMatch(Text,Pattern,[Mode],[IgnoreCase],[Multiline])
    =RegExReplace(Text,Pattern,Replacement,[Instance],[IgnoreCase],[Multiline])
These functions use the VBScript 5.5 regular expression syntax described here: https://learn.microsoft.com/en-us/previous-versions/1400241x(v=vs.85)
and https://drive.google.com/open?id=1qWS2qLib0sL_u6Sh95FuEeZxRzzr4SUE
They are similar to the new Excel functions REGEXTEST, REGEXEXTRACT, and REGEXREPLACE described here: https://insider.microsoft365.com/en-us/blog/new-regular-expression-regex-functions-in-excel
See https://sites.google.com/view/MyExcelToolbox/


2024-07-07 12:13:10

Kiwerry

@ J. Woolley: Thanks very much for the link, looks like a useful reference.

I guess my suggestion has its roots in my having received a number of emails from various Excel sites about the new REGEX functions; it's frustrating to read about something interesting, only to find that it isn't yet available (Excel 365).


2024-07-07 09:57:51

J. Woolley

@Kiwerry
You might be interested in this site: https://exceljet.net/functions
It conveniently identifies the Excel version for every built-in function. A search for EDATE indicates Excel 2003.


2024-07-04 14:55:25

J. Woolley

Re. EDATE(date, months) the Tip says, "The date parameter you use should resolve to a date serial number and not be a textual date."
Excel's DATEVALUE(date_text) converts a textual date into a date serial number. However, DATEVALUE thinks the 21st century ends after 2029, so
DATEVALUE("12/25/30") returns 12/25/1930 instead of 12/25/2030 as might be expected. This could be a problem for the current and future decades.
The following Microsoft article discusses Excel dates:
https://support.microsoft.com/en-us/office/change-the-date-system-format-or-two-digit-year-interpretation-aaa2159b-4ae8-4651-8bce-d4707bc9fb9f
Under the heading "Change the way two-digit years are interpreted," some issues are worth considering (with respect to region "United States"):
For the Windows 10 procedure to "change the way two-digit years are interpreted for all Windows programs that you have installed," in step 1 you should search for "region" instead of "control panel" and select Region, then skip steps 2 and 3 and continue with step 4. Let's assume you "change the upper limit for the century" to 2049 (for example). Now when you type 12/25/30 into a cell the date will be 12/25/2030 and typing 12/25/50 will produce 12/25/1950. However, Excel's DATEVALUE function ignores this change to the Windows Region Calendar; it still thinks the 21st century ends after 2029.
It is worth noting that VBA's DateValue function recognizes a change to the century upper limit appropriately, so VBA.DateValue("12/25/30") returns 12/25/2030 (for this example). Therefore, My Excel Toolbox includes the DATEVALUE2 function to address this issue. Here is an abbreviated version:
    Function DATEVALUE2(Date_Text As String) As Date
        DATEVALUE2 = DateValue(Date_Text)
    End Function
See https://sites.google.com/view/MyExcelToolbox/


2024-07-03 06:09:50

jamies

Especially useful for getting the last day number of a month from a date in a cell
But there is also the DATE() function that takes year, with month numbers that can be positive, or negative offsets from the first day of the year, and day numbers that can also be negative offsets from the first day of the calculated month.
so DATE(1900+years,month+monthoffset+1,-1) is an EOMONTH of the month+monthoffset month

All within the excel date range - as in year calculated, or specified not being before 1900, or 1904
and the month not being FEB or MARCH of the first allowed year, be it 1900 or 1904

Yes there is a 29th Feb in 1900 according to Excel,
but not according to accurate calendars.


2024-07-03 05:04:29

Kiwerry

Thank you Allen.
May I suggest that tips describing a function include a short mention of the first version of Excel which has the function, or its status, if it's still a beta version not widely available?

Why? Reading about an interesting looking function, only to find it's not available, is counterproductive. This doesn't apply to the EDATE function described above, which has been around for a long time, but there are a number of functions introduced in more recent years to which it would apply.

Thanks for your attention and keep well.


This Site

Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.

Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.