Converting text to DateTime

Microsoft Office excel 2007 - upgrade
November 11, 2010 at 02:30:16
Specs: Windows XP
Hi,
I'm not proficient in using excel to manipulate date, the problem was I need to convert a text value to it's equivalent DataTime and compare it in excel.
Input text: 20101027031746 which has a format of YYYYMMDDHHMMSS it need it to be converted to a DataTime value to an excel datatype.
After converting the input DateTime it needs to be in a valid range of
StartTime 20101027031746 and EndTime 20101029195655 values.

Thanks you very much

Gerald


See More: Converting text to DateTime

Report •


#1
November 11, 2010 at 06:23:39
re: Converting the text string into an Excel Date and Time:

I'll break this down into steps so that you can see how it works, then I'll combine it all into 1 formula.

Let's assume you have 20101027031746 in A1.

In A2 enter: =MID(A1,5,2)&"/"&MID(A1,7,2)&"/"&LEFT(A1,4)
In A3 enter: =MID(A1,9,2)&":"&MID(A1,11,2)&":"&RIGHT(A1,2)

This should give you 2 text strings that look like a date and time but are still text:

A2: 10/27/2010
B2: 03:17:46

In B2 enter: =DATEVALUE(A2)
In B3 enter: =TIMEVALUE(A3)

These functions convert text strings into actual Dates and Times, assuming Excel can recognize the string as a valid date or time format.

In C2 enter: =B2+B3 and format it as Custom - m/d/yyyy hh:mm:ss AM/PM to display:

10/27/2010 03:17:46 AM

If you want it all in single cell, it would look like this:

=DATEVALUE(MID(A1,5,2)&"/"&MID(A1,7,2)&"/"&LEFT(A1,4))+TIMEVALUE(MID(A1,9,2)&":"&MID(A1,11,2)&":"&RIGHT(A1,2))

Formatted as Custom - m/d/yyyy hh:mm:ss AM/PM

Note: You might be able to eliminate the DATEVALUE and TIMEVALUE functions since Excel will attempt to use the converted text strings as a date and time, but I'd be more comfortable converting them to actual dates and times just to be safe.

re: After converting the input DateTime it needs to be in a valid range of
StartTime 20101027031746 and EndTime 20101029195655 values.

I'm sure what you mean by "it needs to be in a valid range".

The date and time is what it is. What do you mean by "it needs to be in a valid range"?

Posting Tip: Before posting Data or VBA Code, read this How-To.


Report •

#2
June 3, 2011 at 07:00:59
What a time saver this was. Thank you very much.

Report •

Related Solutions


Ask Question