March 3 2007 (or Mar 3 2007) is not a valid date string in the U.S., either!
U.S. regional format - m/d/yyyy
DATEVALUE = #VALUE!
However, these are valid:
Mar 3, 2007
March 3, 2007
It seems the comma makes all the difference!
--
Biff
Microsoft Excel MVP
"Rick Rothstein (MVP -
VB)" wrote in
message ...
Nothing like consistency within the same product, eh?
Thanks for the confirmation on that.
Rick
"Pete_UK" wrote in message
...
Yes, I can confirm that
Print IsDate("March 3, 2007")
in the Immediate window does produce True, so obviously there is a
difference between the way VBA and spreadsheet functions handle dates.
Pete
On Nov 15, 9:57 am, "Rick Rothstein \(MVP - VB\)"
wrote:
We normally enter dates here as day first followed by month and then
year, so 3 March or 3 March 2007 are recognised, but March 3 2007 or
March 3, 2007 are not, and are treated as strings.
I knew you guys wrote your dates backwards<g, but VB/VBA will still
interpret them as dates here. Likewise, if you go into the VBA editor
and
type/enter this...
Print IsDate("March 3, 2007")
in the Immediate window, it will print True just like if I type/enter
this...
Print IsDate("3 March 2007")
here, it too will print True. VB/VBA will accept anything that is
considered
a date anywhere in the world as a date in any locale (within Date
functions,
of course). I just figured the same date engine was at work within the
Excel
spreadsheet world as well.
So, you learn something new every day <bg
Yep... I did with this thread. Thanks again for pointing it out to me.
Rick