|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:
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:
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.