Time Format Conversion

[Guest]

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!!

 

[Bernard Liengme]

To convert your time values, use =TIME(INT(A1/100),MOD(A1,100),0)
format the cell with Custom Format [h]:mm
best wishes

 

[Guest]

Thanks Bernard! Perfect...
--
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

 

[Gord Dibben]

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


Gord Dibben MS Excel MVP