convert text to time

Hi, I wonder if someone could help me please, I have a column which has 24 hour time listed as follows:


ConsumptionPeriod
0
30
100
130
200
230
300
330
400
430
500

the 0 representing 0:00 and so on

I need the column to be populated with actual 24 times in half hourly increments
does anyone know of a quick way to do this please?

thanks for your help

Originally Posted by dilipandey

Hi victoria,

welcome to the forum.

quick way: - type 1440 anywhere in the worksheet, copy it, select the data, paste special +values+divide, Enter
change the data format to hh:mm:ss and it's DONE

regards,
DILIPandey

<click on below 'star' if this helps>

Thanks DILIPandey for responding so quickly, I tried what you said however this happens:


00:00:00
00:30:00
01:40:00
02:10:00
03:20:00
03:50:00
05:00:00
05:30:00
06:40:00
07:10:00
08:20:00

as you can see the times are not in half hour increments, can you help again, thanks

Try this:

=IF(LEN(A1)=1,"0:00",IF(LEN(A1)=2,"0:"&A1,SUBSTITUTE(A1,RIGHT(A1,2),":"&RIGHT(A1,2))))

You will then have to swap it from general to time format but it should work.

Originally Posted by Marcol

Or maybe

Please Login or Register  to view this content.

Format Cells > Number format > Custom Type:= hh:mm

thanks for your help