Format date and time from one contiguous field

R

RCCruiser

I'm importing a plain text field into a spreadsheet which contains the date
and time as one number in yyyymmddhhmmss format. For example, August 18 at
2:30 PM would be 20080818143000. How can I format the field so it shows as
something more readable like 2008/08/18 14:30:30? I've attempted to create a
custom field category type, but have not been successful. Thank you in
advance.
 
A

akphidelt

You can splice it apart and insert the / and spaces yourself because the data
is pretty standard. However I do not think excel will read this as an actual
date format so unless all you care about is the look, then that wouldn't
work. But this would work as a formula to show the number like you requested.

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

Kevin B

You could use the following formula to convert the string to something
readable as a date/time entry:

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

RCCruiser

That worked perfectly. Thank you!

akphidelt said:
You can splice it apart and insert the / and spaces yourself because the data
is pretty standard. However I do not think excel will read this as an actual
date format so unless all you care about is the look, then that wouldn't
work. But this would work as a formula to show the number like you requested.

=LEFT(A1,4)&"/"&MID(A1,5,2)&"/"&MID(A1,7,2)&" "&
MID(A1,9,2)&":"&MID(A1,11,2)&":"&RIGHT(A1,2)
Click to expand...
 
D

David Biddulph

That would leave it as a text string, as would the simpler
=TEXT(A1,"0000\/00\/00 00\:00\:00")
but
=--TEXT(A1,"0000\/00\/00 00\:00\:00")
will be a date and time which Excel will understand (format the result as
yyyy/mm/dd hh:mm:ss).
 
Top