convert a text date to a true date

You may have seen debates here in the past about using DATE and
DATEVALUE - the problem with the latter (taking strings) is the
regional variations in strings that are recognised as dates in
different countries, so solutions may not work universally. Bearing
this in mind, I would propose the following:

First of all, set up a table of months and the month number, like
this:

Jan 1
Feb 2
Mar 3
Apr 4
May 5
Jun 6
Jul 7
Aug 8
Sep 9
Oct 10
Nov 11
Dec 12

It doesn't really matter where this is (I put mine in M4:N15), but you
should give the table a name like "months" by highlighting the data
and Insert | Name | Define. Then, if your text date is in K4 in the
format you spelled out in your second post, you can use this formula
to convert it to a proper date:

=DATE(RIGHT(K4,4),VLOOKUP(MID(K4,FIND(",",K4)+2,3) ,months,
2,0),MID(K4,LEN(K4)-7,2))

Obviously, adjust the references to K4 to suit your first cell.

Format the cell appropriately, and then copy down if you have other
dates in column K.

The formula should work whichever country you are in.

Hope this helps.

Pete




On Nov 14, 10:56 pm, JR Hester
wrote:
Thanks Tom for that. MAybe I should be a bit more specific.
My dates are text such as
Sunday, March 3, 2002
Wednesday, April 11, 2004
Friday, December 20, 2003
and so forth.



"Tom" wrote:
I have a situation where my dates come to me as 1071114 (107 is the year, 11,
is the month and 14 is the day). I use the formula
=date(left(a1,3),mid(a1,4,2),right(a1,2)) and it converts to 11/14/2007. May
take a bit of modification but it should work for you.

Luck

"JR Hester" wrote:

I am out of ideas. Excel(tm) XP on WinnXP.
I have acquired a spreadsheet "database" from another user. approximayely 5K
records with dates in the dddd, m-d-yy format, although these appear to be
stored as text. Changing the format does not change theway data is displayed.
Cells were originally formatted as General.

Can anyone offer a simple formula method to change these text entries into a
true date format? I am in process of importing info into an Access(tm) database.

Thanks for any suggestions- Hide quoted text -

- Show quoted text -

this doesn't work in the UK, as

March 3, 2002

is not a valid date string. I'll post my solution direct to the OP.

Really? I have no experience with international units, but I figured the
Excel date engine would work the same as the VB/VBA date engine... if the
date engine could interpret something as a date in **any** possible way,
then that is how it interprets it. I'm guessing, based on your posting, that
for the Excel spreadsheet world, this is not the case. Thanks for letting me
know.

Rick

I only discovered this a couple of days ago when a poster from the US
sent me a file to look at and all his dates came up as #VALUE, so I
had to change his formulae from DATEVALUE to DATE before I could look
into the problem he had asked me to look at - so, it was fresh in my
mind!!

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.

So, you learn something new every day bg

Pete

On Nov 15, 4:32 am, "Rick Rothstein \(MVP - VB\)"
wrote:
this doesn't work in the UK, as

March 3, 2002

is not a valid date string. I'll post my solution direct to the OP.

Really? I have no experience with international units, but I figured the
Excel date engine would work the same as the VB/VBA date engine... if the
date engine could interpret something as a date in **any** possible way,
then that is how it interprets it. I'm guessing, based on your posting, that
for the Excel spreadsheet world, this is not the case. Thanks for letting me
know.

Rick

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

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

On Wed, 14 Nov 2007 17:39:27 -0800 (PST), Pete_UK wrote:

Rick,

this doesn't work in the UK, as

March 3, 2002

is not a valid date string. I'll post my solution direct to the OP.

Pete

Thanks for pointing that out, Pete. I just confirmed it here by changing my
Regional settings to UK.
--ron

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

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

On Thu, 15 Nov 2007 13:13:57 -0500, "T. Valko" wrote:

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

The comma does NOT make a difference if your regional settings are
English(United Kingdom). Still get the #VALUE! error
--ron

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