Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 365. 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: Determining Differences Between Dates.

Determining Differences Between Dates

Written by Allen Wyatt (last updated August 5, 2024)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 365


7

When you are programming Excel macros, you should know that dates are stored internally, within variables, as serial numbers. The serial number represents the number of days elapsed since a starting "base date," specifically since 1 January 100. This means that you can perform math with the serial numbers, if desired. You can, for instance, find the number of days between two dates by simply subtracting the dates from each other.

If you want to get fancier in your date calculations, you can use the DateDiff function. This function allows you, for instance, to determine the number of weeks or months between two dates. In order to use the function to find this type of information, you would do as follows:

iNumWeeks = DateDiff("ww", dFirstDate, dSecondDate)
iNumMonths = DateDiff("m", dFirstDate, dSecondDate)

The first line determines the number of weeks between the two dates, and the second determines the number of months between them.

Remember that the DateDiff function is a macro (VBA) function, not a worksheet function. Excel handles a range of dates in worksheets that begin with January 1, 1900. In VBA, however, dates can begin (as already noted) in the year 100. That means that macros can handle a much larger range of dates, including dates prior to those handled natively by Excel.

Note:

If you would like to know how to use the macros described on this page (or on any other page on the ExcelTips sites), I've prepared a special page that includes helpful information. Click here to open that special page in a new browser tab.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (9046) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 365. You can find a version of this tip for the older menu interface of Excel here: Determining Differences Between Dates.

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

Selecting Lots of Graphics

Need to select a lot of graphics in the document? Here's an easy way to do it using tools available on the Drawing toolbar.

Discover More

Converting Forced Text to Numbers

If you have some numbers stored in cells that are formatted as text, you may get some surprises when you try to use those ...

Discover More

Locking Track Changes On

Track Changes is a powerful tool to help you know what has been changed in a document. You may want to make sure that ...

Discover More

Save Time and Supercharge Excel! Automate virtually any routine task and save yourself hours, days, maybe even weeks. Then, learn how to make Excel do things you thought were simply impossible! Mastering advanced Excel macros has never been easier. Check out Excel 2010 VBA and Macros today!

More ExcelTips (ribbon)

Filling a Range of Cells with Values

When writing a macro, you may want to fill a range of cells with different values. The easiest way to do this is to use ...

Discover More

Calculating the Distance between the Top of the Window and Row 1

Normally Excel positions a UserForm in the center of your screen. You may want to position the form elsewhere, more ...

Discover More

Trimming Spaces from Strings

Need to get rid of extraneous spaces before or after the text in a string? VBA provides three different functions you can ...

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 minus 7?

2024-08-05 07:38:50

Brian

It may be worth noting for historians that even though a macro can handle dates as far back 1/1/100 it can't handle the anomaly that in the UK the dates from the 3rd to 13th September 1752 were missing. The calendar went from Wednesday 2nd to Thursday 14th when Great Britain adopted the Gregorian calendar.


2020-02-16 09:09:55

Alan Elston

@ Karim K….See also here: http://www.excelfox.com/forum/showthread.php/2345-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=12309#post12309 ….


2020-02-16 09:06:29

Alan Elston

Hello Karim K. ……
One way would be to have a macro that starts running every time your workbook is open. That could check the date in any cells of your choice for a date, say, column C, and if found do your required calculations and produce your required results.
…. .
It could be written in such a way that macro also runs every time you type a new date in column C
….
For example, open this workbook. : https://app.box.com/s/ti0n1wj62hcd2qmhcg5kiqle1sya79ux
…..

It will put the count you want alongside in column D, and a few other things based on Allen Wyatt’s notes above and a few other things I felt like doing.
……
Alan Elston

(P.S. I am taking a guess that you meant 2/15/2020 to give you a 1 , not 2/15/2019)


2020-02-15 15:08:06

Karim K.

I am trying to let the user input a Date in a cell. Using the input date and today's date, I would calculate how many days have passed since the user's input date.

Example: User inputs "2/15/2019" in cell (C4) - The next day it shows "1 Day/s" and so on.

I've searched and tried many things but failed, I am no expert with excel or VBA by any means, so I am not even sure if that is possible.


2019-08-12 11:44:32

Roy

Datedif() ALSO has 2-3 failures available. 2-3 of the options give wrong answers.

This is a known defect and lack of interest by Excel in fixing it is probably why they don't document it. Likely they left it in though for any spreadsheets written using it by trusting souls. Those souls only had to fix the formulas that used the defective operations.

I don't remember the defective options off-hand but I'm sure the internet does:

https://support.office.com/en-us/article/datedif-function-25dba1a4-2812-480b-84dd-8b32a451b35c says the "MD" option may give wrong naswers. It also says it was intended for compatibility with 1-2-3.

Other sites add information and it looks like it picks the second month entered as "the" month toi base to calculation on which causes difficulties if the two months involved do not have the same number of days. For example, February and any other month will cause 2-3 days to be added to the answer expected if January is the month of the second date. And vice versa. And some palces suggest it is less straightforward than that, either way, it is not only an obvious difficulty programmers should have expected (and MAY have, but kept it so because the 1-2-3 function worked (worongly) that way - or not, maybe the 1-2-3 function worked perfectly).

In any case, it has at least the one defect and I seem to recall there was a second, but you know how recall goes as one gets old...

In any case though, most all of it works so if ya wanna use it, it's there to be used!


2019-08-12 11:33:22

Dave

Just a caution...

When working with dates, always consider the possibility that your date serial numbers may have fractional portions. That's because everything to the right of the decimal point represents time with a given integer date. It's very slick.

Some examples of date serial numbers and what they represent:
43689.000000 August 12, 2019 -- specifically at 12:00 midnight at the start of the day.
43689.500000 August 12, 2019 at noon
43689.750000 August 12, 2019 at 6:00 pm (18.00)
43689.999988 August 12, 2019 at 11:59 pm (23.59.59)

Depending on your needs and your data, it may be good to truncate date values to make them integers before subtracting one from the other.

Also, when working with dates, you need to decide whether to add one to the result or not, depending on your needs.

For example, if both of your dates are August 12, do you want your result to be zero days or one day? Merely subtracting the values will give you zero days, but that may not match your intent. In that case, modify your formula to add 1 to the result.

Similarly, if your two dates are August 12 and 14, do you want the result to be 2 days of 3?


2019-08-10 14:46:05

Dean Cardno

Datedif is also available as a worksheet function (at least up to Excel 2013). MS stopped documenting it early on, for some reason, but it has remained in the product. The syntax is different:
= Datedif(<first date>, <second date>, <format>), where 'format is "Y" for years, "M" for months, "YM" for months ignoring full years, and "MD" for days ignoring full months. A formula of:
=DATEDIF(H16, H22, "Y") & " Years, " & DATEDIF(H16, H22, "YM") & " Months, and "& DATEDIF(H16, H22, "MD") & " days"

will return a result like:
3 Years, 2 Months, and 20 days


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.