Microsoft Office excel 2007 - upgrade

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 muchGerald

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

looklike a date and time but are still text:A2: 10/27/2010

B2: 03:17:46In 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 thisHow-To.

What a time saver this was. Thank you very much.

Ask Your Question

Weekly Poll