Hello everyone, I have a spreadsheet produced from the reporter of a system
we use. It formats the dates and times as 20070131 and 1530 which I need to
convert to a date/time format excel recognizes. I have been able to use Text
to Columns to break up the date and reassemble it using a formula. The time
field however is difficult because it will populate as a general format
something like this:

DATE TIME
20070823 12 ( which is 00:12 )
20070823 123 ( which is 01:23 )
20070823 1425 ( which is 14:25 )

If I try to use Text to Columns on the time field it will left justify
making it impossible. I've also tried a custom format which looks good on the
sheet and will display 12 as 0012 for example, but the Text to Columns
function does not mind the zero's. Is there a way for me to convert a general
field as shown in my example to a time field formated as say 00:12 or 14:25?

Thanks for any help!!

--
If you can read this, thank a Teacher...
If your reading it in english, thank a Veteran!

To convert your time values, use =TIME(INT(A1/100),MOD(A1,100),0)
format the cell with Custom Format [h]:mm
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"Telobamipada" wrote in message
...
Hello everyone, I have a spreadsheet produced from the reporter of a
system
we use. It formats the dates and times as 20070131 and 1530 which I need
to
convert to a date/time format excel recognizes. I have been able to use
Text
to Columns to break up the date and reassemble it using a formula. The
time
field however is difficult because it will populate as a general format
something like this:

DATE TIME
20070823 12 ( which is 00:12 )
20070823 123 ( which is 01:23 )
20070823 1425 ( which is 14:25 )

If I try to use Text to Columns on the time field it will left justify
making it impossible. I've also tried a custom format which looks good on
the
sheet and will display 12 as 0012 for example, but the Text to Columns
function does not mind the zero's. Is there a way for me to convert a
general
field as shown in my example to a time field formated as say 00:12 or
14:25?

Thanks for any help!!

--
If you can read this, thank a Teacher...
If your reading it in english, thank a Veteran!

Thanks Bernard! Perfect...
--
If you can read this, thank a Teacher...
If your reading it in english, thank a Veteran!


"Bernard Liengme" wrote:

To convert your time values, use =TIME(INT(A1/100),MOD(A1,100),0)
format the cell with Custom Format [h]:mm
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"Telobamipada" wrote in message
...
Hello everyone, I have a spreadsheet produced from the reporter of a
system
we use. It formats the dates and times as 20070131 and 1530 which I need
to
convert to a date/time format excel recognizes. I have been able to use
Text
to Columns to break up the date and reassemble it using a formula. The
time
field however is difficult because it will populate as a general format
something like this:

DATE TIME
20070823 12 ( which is 00:12 )
20070823 123 ( which is 01:23 )
20070823 1425 ( which is 14:25 )

If I try to use Text to Columns on the time field it will left justify
making it impossible. I've also tried a custom format which looks good on
the
sheet and will display 12 as 0012 for example, but the Text to Columns
function does not mind the zero's. Is there a way for me to convert a
general
field as shown in my example to a time field formated as say 00:12 or
14:25?

Thanks for any help!!

--
If you can read this, thank a Teacher...
If your reading it in english, thank a Veteran!

If your grammatical construction is incorrect, don't thank either one of them.


Gord Dibben MS Excel MVP

On Thu, 23 Aug 2007 13:06:02 -0700, Telobamipada
wrote:

If you can read this, thank a Teacher...
If your reading it in english, thank a Veteran!