convert text to a date

[eah03]

How do I convert the text below to a date

Feb 25 2003 12:00:00:000A

Thanks in advance

 

[Norman Harker]

Hi eah03!

For just the date use:

=--(MID(A1,5,2)&" "&LEFT(A1,3)&" "&MID(A1,8,4))
Format as a date

Without the -- you get the date as text. Since dates are numbers,
the -- coerces the result to the date serial number.

If you want the whole date and time:

=--(MID(A1,5,2)&" "&LEFT(A1,3)&" "&MID(A1,8,4)&"
"&MID(A1,13,2)&":"&MID(A1,16,2)&":"&MID(A1,19,2)&"."&MID(A1,22,3))

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[Ron Rosenfeld]

How do I convert the text below to a date?

Feb 25 2003 12:00:00:000AM

Thanks in advance.

This somewhat obscure formula will do it:

=--LEFT(SUBSTITUTE(A1," ",", ",2),FIND("~",SUBSTITUTE(A1," ","~",3)))


--ron

 

[Norman Harker]

Hi Ron!

I'm getting #VALUE! unless I use US date settings. The trouble is that
Feb 25, 2004 is returned as Text with non-US settings.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[David Byrne]

Hi,


=DATEVALUE(MID(A1,5,2)&LEFT(A1,3)&MID(A1,8,4))


Gives the serial number (37,677) for the date.

Format cell to suit local custom.


David

 

[Ron Rosenfeld]

Hi Ron!

I'm getting #VALUE! unless I use US date settings. The trouble is that
Feb 25, 2004 is returned as Text with non-US settings.

Thanks for pointing that out.

That'll also happen with DATEVALUE. I should not have assumed that the OP was
using US regional settings. But since the original data was US-centric, I made
that assumption.

So if he is using US settings, then my formula should work


--ron

 

[Norman Harker]

Hi Ron!

Don't get me wrong, I liked the solution. A lot shorter than mine. We
tend to use dd-mm-yyyy but I quite often see the Feb 28, 2004. I can't
see why it shouldn't be in the series of dates that will be
interpreted by both Regional Settings.

DATEVALUE will return the correct date in both Regional settings as
long as you use one of the unequivocal date forms:

12 January 2002
12-January-2002
12/January/2002
12 Jan 2002
12-Jan-2002
12/Jan/2002
2002-01-12
2002/01/12
2002/1/12

It's the same with other date functions which allow date strings.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.