Raw Data - Formatting a Date field

[PowerUserInTraining]

I have system generated file that I work with regularly that provides date
and time
information like this: 20080919021412.000000-240

I'm having difficulty converting that field to show the following date/time
format:
9/19/08 2:14:12 AM

Any suggestions?

 

[Fred Smith]

Try this:

=date(left(a1,4),mid(a1,5,2),mid(a1,7,2))+time(mid(a1,9,2),mid(a1,11,2),mid(a1,13,2))

Regards,
Fred.

 

[Mike H]

How about this long winded solution

=TEXT(DATE(LEFT(A1,4),MID(A1,5,2),MID(A1,7,2)),"mm/dd/yy")&"
"&TEXT(TIME(MID(A1,9,2),MID(A1,11,2),MID(A1,13,2)),"hh:mm:ss AM/PM")

Mike

 

[Dave Peterson]

I could use a helper cell with a formula like:

=--TEXT(LEFT(A1,14),"0000\/00\/00 00\:00\:00")

And give it a number format of:
mm/dd/yyyy h:mm:ss AM/PM

 

[Rick Rothstein]

I'm not sure if using slashes will allow for varying regional
interpretations of month/day ordering in that format; but, given the
international date ordering inside the original text, using dashes can never
be misinterpreted (plus it saves two characters)...

=--TEXT(LEFT(A1,14),"0000-00-00 00\:00\:00")

 

[Dave Peterson]

Just curious...

Is the dash format ISO compliant while the slash format is not?

 

[Rick Rothstein]

That is my understanding. See the General Principles section of this link...

http://en.wikipedia.org/wiki/ISO_8601

I know Excel will unambiguously evaluate dates using the YYYY-MM-DD format
correctly; what I am not sure of is how it interprets the YYYY/MM/DD format;
that is, I'm not sure if Excel will always unambiguously interpret the
middle section as month and right section as days when that format is used
with a 4-digit year. It may, in fact, do that, but I don't have the
international experience to be sure it will do that under all possible
regional settings.

 

[Dave Peterson]

I have the same lack of experience with international issues. I'll try to
remember to use the dash (but keep an eye out <vbg>.)

That is my understanding. See the General Principles section of this link...

http://en.wikipedia.org/wiki/ISO_8601

I know Excel will unambiguously evaluate dates using the YYYY-MM-DD format
correctly; what I am not sure of is how it interprets the YYYY/MM/DD format;
that is, I'm not sure if Excel will always unambiguously interpret the
middle section as month and right section as days when that format is used
with a 4-digit year. It may, in fact, do that, but I don't have the
international experience to be sure it will do that under all possible
regional settings.

 

[PowerUserInTraining]

Thanks everyone. All of the suggestions provided worked.