convert text to a date

  • Thread starter Thread starter Guest
  • Start date Start date
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.
 
How do I convert the text below to a date?

Feb 25 2003 12:00:00:000AM

Thanks in advance.
Click to expand...

This somewhat obscure formula will do it:

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


--ron
 
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.
 
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
 
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.
Click to expand...

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
 
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.
 
Back
Top