Bug in VBA function DateDiff

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
24,329
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.
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
20,823
Office Version
  1. 365
Platform
  1. Windows
Hi Mike,

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

VBA Code:
? DateDiff("d","1/7/1900","1/8/1900")
 
Upvote 0

Micron

Well-known Member
Joined
Jun 3, 2015
Messages
4,641
Office Version
  1. 365
Platform
  1. Windows
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.
 
Upvote 0

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
24,329
That is odd. That date came from running this
VBA Code:
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
 
Upvote 0

Leo Skywalker

Board Regular
Joined
Jun 24, 2017
Messages
207
Office Version
  1. 365
  2. 2016
  3. 2013
Platform
  1. Windows
  2. Web
It's returning 1 here, too. Excel 2016 onWin7.

1667437309362.png
 
Upvote 0

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
38,126
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
?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#)
 
Upvote 0

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
40,136
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
  2. MacOS
I see the same as Mike in 365 on a Mac. Definitely looks like a bug.
 
Upvote 0

Micron

Well-known Member
Joined
Jun 3, 2015
Messages
4,641
Office Version
  1. 365
Platform
  1. Windows
Year 1 for Excel for Mac is 1904. For pc it 1900. Could that have anything to do with it?
 
Upvote 0

Forum statistics

Threads
1,186,710
Messages
5,959,313
Members
438,412
Latest member
jldccc2007

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top