Home » Forums » Open Source Learning Center » Question and Answer

please help - convert times into hourly intervals

Submitted by vin1602 on 26 September, 2013 - 15:02

Any help gratefully received. I have a s/sheet with thousands of times in a column (eg col1 below in 24 hour format) and would like these in an adjacent column in hourly intervals (eg in Col2 below
e.g.
Col1 ColB
00:23 0 - 1
02:54 2 - 3
09:07 9 - 10
15:24 15 - 16

Is there a formula I can run to enable?

many thanks
Vin

‹ Modify code to pre-populate textboxes based on database? vba excel userform date calculation ›
»

Please try the following

Submitted by Jeanbar on 30 September, 2013 - 22:30.

Please try the following formula: =TEXT(INT(MOD(A1*1;1)*24);"0")&"-"&TEXT(INT(MOD(A1*1;1)*24)+1;"0")

nb: separators as European format. Might be simple commas in other parts of the world.

»