# Solved: What's wrong with DateDiff function in VBA?



## guo1121 (Nov 9, 2007)

I did some experiments with DateDiff function in VBA. We know 1 is 1/1/1900, 2 is 1/2/1900, 32 is 02/1/1900, 33 is 02/02/1900, 61 is 03/01/1900, and 62 is 03/02/1900. However DateDiff("m", 1, 2) returns 1; DateDiff("m", 32, 33) returns 1; but DateDiff("m", 61, 62) returns 0.

I am confused why it is the case. My understanding is that all of the three should return 0 because the two date falls in the same month in each case. More surprising is that for DateDiff("m", 1, 2) and DateDiff("m", 32, 33), they return 1, but DateDiff("m", 61, 62) returns 0.

Anyone has any idea on this? Not crucial or important in my codes, but I'd like to know the reason. Thanks. Below is the code for my experiments


Sub test

Cells(1, 1) = DateDiff("m", 1, 2)
Cells(2, 1) = DateDiff("m", 2, 3)
Cells(3,1) = DateDiff("m", 32, 33)
Cells(4, 1) = DateDiff("m", 33, 34)
Cells(5, 1) = DateDiff("m", 61, 62)
Cells(6, 1) = DateDiff("m", 92, 93)

End Sub


----------



## JohnWilson (Nov 22, 2007)

When you count month intervals DateDiff counts the number of firsts of the month between the two dates including the first date. All your examples should return "1" as they include a first of the month. 

I don't know why the last doesn't but maybe it's a leap year miscalculation? 

This does return "1"
DateDiff("m", #1/3/1900#, #2/3/1900#) (UK notation for date)


----------



## guo1121 (Nov 9, 2007)

JohnWilson said:


> This does return "1"
> DateDiff("m", #1/3/1900#, #2/3/1900#) (UK notation for date)


Hi, John,

That's weird. On my machine (US notation for date), DateDiff("m", #3/1/1900#, #3/2/1900#) returns 0 instead same as DateDiff("m", 61, 62). So is DateDiff("m", #1/1/1900#, #1/2/1900#), DateDiff("m", #2/1/1900#, #2/2/1900#), DateDiff("m", #4/1/1900#, #4/2/1900#), DateDiff("m", #5/1/1900#, #5/2/1900#), and so on.


----------



## guo1121 (Nov 9, 2007)

Credit to anhn from VBForum.

http://www.vbforums.com/showthread.php?p=3196827#post3196827

This is an interesting issue not many people know.

That is really a bug but not the bug in VBA/VB6, it is a bug on spreadsheet that Microsoft intended to create and keep it:

"Long long time ago," there was a spreadsheet app called "Lotus 1-2-3", this app had a bug on date as it miscalculated the year 1900 as a leap year that made the "date" 29/02/1900 is a valid date.
In fact, 29/02/1900 is an invalid date because the year 1900 is not a leap year.

Excel was a much "younger" spreadsheet app that came from a strong family. Excel wanted to kill L123 ASAP. So, Excel allowed users who knew only one kind of spreadsheet in their lives to have an easy way to use Excel with existing L123 files that can be easily imported to or opened with Excel and to make it 100% compatible on date calcutions, Excel had to keep the bug that came from L123.

However, Excel spreadsheet has another date system that does not contains that bug, "1904 date system", that you can find in [Options]>[Calculation]-(Workbook options).

On a cell, if you type 29/02/1900 (or 02/29/1900) it will be accepted as a date.
But in VBA code, that is an invalid code:
Code:

? CDate(DateSerial(1900,2,29)) 01/03/1900 ? CDate("28/2/1900") 28/02/1900 ? CDate("29/2/1900") '<=== Type mismatch error

That is the reason why CDate(1) returns 12/31/1899, but enter 1 in a date formated cell that will be auto convert


----------



## Drill123 (Apr 16, 2008)

But it doesn't explain this strange *VBA* behaviour:

This procedure

*Sub Test()*
Cells(1, 1) = DateSerial(2007, 6, 1) - DateSerial(2007, 5, 31)
Cells(2, 1) = Format(DateSerial(2007, 6, 1) - DateSerial(2007, 5, 31), "d/m/yyyy")
Cells(3, 1) = CDate(Format(DateSerial(2007, 6, 1) - DateSerial(2007, 5, 31), "d/m/yyyy"))
Cells(4, 1) = DateDiff("d", CDate(DateSerial(2007, 5, 31)), CDate(DateSerial(2007, 6, 1)))
Cells(5, 1) = DateDiff("M", CDate(DateSerial(2007, 5, 31)), CDate(DateSerial(2007, 6, 1)))
Cells(6, 1) = DateDiff("y", CDate(DateSerial(2007, 5, 31)), CDate(DateSerial(2007, 6, 1)))
*End Sub*

returns results:

*1 - OK
31/12/1899 - ???
01/01/1900 - OK
1 - OK
1 - ???
1 - ???
*

It's not spreadsheet but *VBA* matter because spreadsheet's DATEDIF function *works correctly*!!! Even with 1900 date system.


----------

