Raw Data - Formatting a Date field

  • Thread starter Thread starter PowerUserInTraining
  • Start date Start date
P

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?
 
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.
 
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
 
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
 
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")
 
Just curious...

Is the dash format ISO compliant while the slash format is not?
 
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.
 
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>.)

Rick said:
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.
Click to expand...
 
Back
Top