Raw Data - Formatting a Date field
 

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?

Raw Data - Formatting a Date field
 

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.

"PowerUserInTraining" wrote
in message ...
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?

Raw Data - Formatting a Date field
 

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

"PowerUserInTraining" wrote:

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?

Raw Data - Formatting a Date field
 

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



PowerUserInTraining wrote:

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?

--

Dave Peterson

Raw Data - Formatting a Date field
 

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")

--
Rick (MVP - Excel)


"Dave Peterson" wrote in message
...
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



PowerUserInTraining wrote:

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?

--

Dave Peterson

Raw Data - Formatting a Date field
 

Just curious...

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



Rick Rothstein wrote:

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")

--
Rick (MVP - Excel)

"Dave Peterson" wrote in message
...
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



PowerUserInTraining wrote:

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?

--

Dave Peterson

--

Dave Peterson

Raw Data - Formatting a Date field
 

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.

--
Rick (MVP - Excel)


"Dave Peterson" wrote in message
...
Just curious...

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



Rick Rothstein wrote:

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")

--
Rick (MVP - Excel)

"Dave Peterson" wrote in message
...
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



PowerUserInTraining wrote:

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?

--

Dave Peterson

--

Dave Peterson

Raw Data - Formatting a Date field
 

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 Rothstein wrote:

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.

--
Rick (MVP - Excel)

"Dave Peterson" wrote in message
...
Just curious...

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



Rick Rothstein wrote:

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")

--
Rick (MVP - Excel)

"Dave Peterson" wrote in message
...
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



PowerUserInTraining wrote:

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?

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson

Raw Data - Formatting a Date field
 

Thanks everyone. All of the suggestions provided worked.

"Dave Peterson" wrote:

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 Rothstein wrote:

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.

--
Rick (MVP - Excel)

"Dave Peterson" wrote in message
...
Just curious...

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



Rick Rothstein wrote:

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")

--
Rick (MVP - Excel)

"Dave Peterson" wrote in message
...
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



PowerUserInTraining wrote:

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?

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson