Creating number pattern to replicate 24h time

[thetalldude]

Hi all,

I am at wits end to create a number pattern to replicate 24h time.

I require the format to be 'general' and the pattern look like (correctly formatted time in brackets):

001 (00:01), 002 (00:02),...059 (00:59), 100 (01:00),...2259 (22:59), 2300 (etc), 2301,...2359, 2400

I am positive that a simple formula will do the trick, yet I fall down in part where it skips from the end of one hour to the beginning of the next.

Any insights would be appreciate!

[Armando Montes]

What about 060-099?
Try this custom format: 0\:00

[thetalldude]

I see what you mean, however I have data with time stamps that are not theoretically correct, e.g. start with 001 and end with 2400; its basically 24h time without the colons. Its doesn't go to 060-099. Its making it very hard to extract from the data because I can't yet construct lists to replicate the time stamps.

[:) Sixthsense :)]

I see what you mean, however I have data with time stamps that are not theoretically correct, e.g. start with 001 and end with 2400; its basically 24h time without the colons. Its doesn't go to 060-099. Its making it very hard to extract from the data because I can't yet construct lists to replicate the time stamps.

I believe the below thread will help you to get rid of this issue.

http://www.excelforum.com/excel-gene...sing-time.html

Please let us know in case of any further assistance.

[FDibbins]

Why do you want to have the time shown in that format anyway?

[Armando Montes]

You can do it using the custom format: 0\:00, try it

[thetalldude]

Thanks all - I am across the line with this help!

Cheers