DateDiff function unexpectedly returning another date or a default

Question

I'm trying to use the DateDiff function for a macro in excel using VBA.

To my understanding the code is supposed to return an integer value.

Here are the examples given online at this location: http://www.techonthenet.com/access/functions/date/datediff.php

-DateDiff ("yyyy", #15/10/1998#, #22/11/2003#) would return 5

-DateDiff ("m", #15/10/2003#, #22/11/2003#) would return 1

-DateDiff ("d", #15/10/2003#, #22/11/2003#) would return 38

Here is the code that I used:

Sub macrotest()

Dim dateNow, dateThen, dateFinal As Date

dateNow = Format(Sheet1.Cells(2, 2), "DD/MM/YY")
dateThen = Format(Sheet1.Cells(3, 2), "DD/MM/YY")
dateFinal = DateDiff("d", dateNow, dateThen)

Sheet1.Cells(5, 2) = dateFinal


End Sub

Lastly, This is what I had in my excel rows:

B2: 5/30/12

B3: 3/30/12

B5: 12:00:00 AM (this cell was supposed to have the result listed).

-I got either dates that excel defaults to, or 12:00.

Any Ideas? Thanks!

Your dates in the excel file are actual dates or simple text strings? — Siddharth Rout, May 30 '12 at 19:55
Check what format cell B5 is currently set to. Is it set to a numeric format? — mellamokb, May 30 '12 at 19:56
Oops..stupid mistake, but still not fixed. in cell B5 (where the answer was supposed to be listed) it was formatted as "custom" so I changed it to date, but the date that is listed in B5 is this: 1/0/1900 — Mike Kellogg, May 30 '12 at 20:03

score 3 · Accepted Answer · answered May 30 '12 at 20:04

According to the help system, DateDiff returns a Long.

Your code declares two Variants and one Date type. Instead of

Dim dateNow, dateThen, dateFinal As Date

you probably meant

Dim dateNow As Date, dateThen As Date, dateFinal As Long

This code worked for me. You may need to tweak it slightly to apply it to your particular situation (I used ActiveSheet to make the testing go faster)

Sub macrotest()

Dim dateNow As Date, dateThen As Date, dateFinal As Long

dateNow = ActiveSheet.Cells(2, 2).value
dateThen = ActiveSheet.Cells(3, 2).value
dateFinal = DateDiff("d", dateThen, dateNow)

ActiveSheet.Cells(5, 2) = dateFinal

End Sub

Also I believe you have dateNow and dateThen reversed in your DateDiff function call.

+1 Exactly my thoughts which I was almost half way typing but discarded LOL — Siddharth Rout, May 30 '12 at 20:06
that worked! Also, you were right about the date variables being swapped. Thanks a lot mate! — Mike Kellogg, May 30 '12 at 20:10

DateDiff function unexpectedly returning another date or a default

1 Answers1