Date Format difficulty

[sandoztek]

I have been using the code below to substract 2 given dates to get days:
nbDays = DateDiff("d",date2 - date1)

date1 example : "2009-03-06" ( 06 march )

date2 example : "02Feb09"

I have
date2 = Left(date2, 2) & "-" & Mid(date2, 3, 3) & "-" & Right(date2,2)
then
date2= Format(date2, "dd/mm/yyyy")

I then substract with DateDiff and it worked nicely

Since I had to reinstalled windows and Excel, the format doesn't work any more and the date "02-Feb-09" remains unchanged, which brings up an exception in DateDiff.
I can't figure out why. Any ideas?

 

[SkipVought]

Hi,

faq68-5827

First off, STRINGS are not dates. Dates are NUMBERS, as explained in the FAQ.

dim date1 as string, date2 as string
dim dte1 as date, dte2 as date
dim DiffDays as integer

date1 =  "2009-03-06" 
dte1 = DateValue(date1)

date2 = "02Feb09" 
dte2 = DateValue(left(date2,2) & "/" & mid(date2,3,3) & "/" & right(date2,2))

DiffDays = dte2 - dte1

Skip,
[sup]Don't let the Diatribe...
talk you to death![/sup][sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[sandoztek]

I 'll take your code, which is nicer/neater than mine.
This said, it doesn't work either, ..... just because when I re installed Windows etc.., I forgot ....to modify the Regional Settings (environment is in French, but I use Excel & VB, VBA in English UK).
It's all right now, both my code & yours.
Thank you for helping.

 

[missinglinq]

Note that the syntax for DateDiff() in your original post

nbDays = DateDiff("d",date2 - date1)

was not correct. The minus sign between date2 and date1 needs to be a comma

nbDays = DateDiff("d",date2, date1)

Skip's code works using the minus sign because he's not using the DateDiff() function.

The Missinglinq

Richmond, Virginia

There's ALWAYS more than one way to skin a cat!

 

[sandoztek]

my code did contain a comma in the Diff function,
I was just absentminded when I put a minus sign in my text

 

[SkipVought]

ALSO, realizing that this is NOT a real date...

date2= Format(date2, "dd/mm/yyyy")

in practive, it is much safer to use an unambiguous format...

date2= Format(date2, "yyyy/mm/dd")

What Excel ends up doing is CONVERTING the best it can, from string to numeric date value.


Skip,
[sup]Don't let the Diatribe...
talk you to death![/sup][sub]
Just traded in my old subtlety...
for a NUANCE![/sub]