convert a text date to a true date

Are you now saying that my SUBSTITUTE formula above does, in fact, work in
locales other than the US (provided the comma is present)?

No. I was simply pointing out that DATEVALUE doesn't recognize mmmm d yyyy
as a valid date string in either U.S. or UK regional settings. You would
think (at least, I would think) that mmmm d yyyy should be a valid date
string in the U.S. since it *is* a valid date format.

A1 = 3/3/2007

Custom format as mmmm d yyyy

A1 displays March 3 2007

--
Biff
Microsoft Excel MVP


"Rick Rothstein (MVP - VB)" wrote in
message ...
The OP specified the format (with the comma) in the second response (to JR
Hester) which is what I posted my

=--SUBSTITUTE(A2,LEFT(A2,FIND(" ",A2)),"")

formula against. Pete's first reply specifically said in his first reply
to me that..

"this doesn't work in the UK, as

March 3, 2002

is not a valid date string"

Note the comma in his message. Are you now saying that my SUBSTITUTE
formula above does, in fact, work in locales other than the US (provided
the comma is present)?

Rick


"T. Valko" wrote in message
...
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 backwardsg, 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