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.

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)

"RCCruiser" wrote:

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.

That worked perfectly. Thank you!

"akphidelt" wrote:

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)

"RCCruiser" wrote:

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.

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)

--
Kevin Backmann


"RCCruiser" wrote:

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.

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).
--
David Biddulph

"Kevin B" wrote in message
...
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)

--
Kevin Backmann


"RCCruiser" wrote:

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.