Covert Time into Number

[niladri20005]

Hi All,

In a columns I have the time like below
18:10
19:25
19:52
20:15
21:45
00:12
00:23
00:55
01:45
02:34
03:36
04:14
05:18
06:17

I want to show the time 6-8 (18:00-20:00) as 1, 8-10 (20:00-22:00) as 2
10-12 (20:00-00:00) as 3, 00:00-02:00 (00:00-02:00) as 4 and so on...

will I have to write macro? or is there any better way to that?

Please help!!

[ChemistB]

I would use a VLOOKUP. Make a table of your times with their corresponding numbers in the next column. Start with the earliest time i.e. 0:00 and 4 through to 22:00 and 3 (See attachment)
Then in the column next to your actual times, use this formula

=VLOOKUP(A4,$H$3:$I$14,2)
where A4 is your time, H3:I14 is your lookup table and 2 means return column 2.
Does that work for you?

[NBVC]

Try:

=INT(A1*24/2)+IF(A1<TIME(18,0,0),4,-8)