Bug in VBA function DateDiff

[mikerickson]

This is about the VBA function DateDiff, not the worksheet function DATEDIF.

I've recently found that DateDiff("d", "1/7/1900", "1/8/1900") returns 0 instead of the expected 1.
And there are 3 other dates that have a 0 DateDiff from the next day, 5/6/1900, 6/4/1922, 9/16/1989

This bug also effects the results of date ranges than include those dates.
Is this a known bug?

Thank you for reading.

 

[Domenic]

Hi Mike,

When I enter the following line in the Immediate Window it returns 1 . . .

? DateDiff("d","1/7/1900","1/8/1900")

 

[Micron]

Not for me it doesn't. Not in Access or Excel. For DateDiff("d", "1/7/1900", "1/8/1900") I get 1. Maybe there is an option setting you have that I don't.
My Excel version is 365 version 2205. Given that you used 1900 it can't be about the 1904 date system.

 

[mikerickson]

That is odd. That date came from running this

Dim enddate As Date
Dim i As Long, pointer As Long

 For i = 1 To Date
    enddate = enddate + 1
  
    If DateDiff("d", enddate, enddate + 1) <> 1 Then
        pointer = pointer + 1
        Cells(pointer, 13) = enddate
        Cells(pointer, 14) = enddate + 1
        Cells(pointer, 15) = DateDiff("d", enddate, enddate + 1)
    End If
    
Next i

And I got 4 rows, for 1/7/1900, 5/6/1900, 6/4/1922 and 9/16/1989
But of the four, the first 3 return 1 in the immediate box, but and only the last one returns 0

?DateDiff("D", "9/16/1989", "9/17/1989") returns 0

 

[Leo Skywalker]

It's returning 1 here, too. Excel 2016 onWin7.

 

[Domenic]

?DateDiff("D", "9/16/1989", "9/17/1989") returns 0

This too returns 1 for me.

If DateDiff("d", enddate, enddate + 1) <> 1 Then

This statement never evaluates to True for me.

 

[Rick Rothstein]

?DateDiff("D", "9/16/1989", "9/17/1989") returns 0

My system returns 1 for that. I am using XL2019 if that matters. What version of Excel are you using Mike? Just out of curiosity, what do you get if you specify the dates using the # sign instead of quote marks?

?DateDiff("D", #9/16/1989#, #9/17/1989#)

 

[mikerickson]

I get 0 when I put that in the immediiate window.

I'm using Excel 2019 for Mac, v 16.66.1

 

[RoryA]

I see the same as Mike in 365 on a Mac. Definitely looks like a bug.

 

[Micron]

Year 1 for Excel for Mac is 1904. For pc it 1900. Could that have anything to do with it?