You're keeping track of when certain events that are happening during the day and, let's say, you want to count how many times the event occurs in 2 hour increments. So using a 24 hour clock, you want to know how many between 0 -2, 2-4, etc., 20-22, 22-24.
What I did was this: First I found the internal general number Excel keeps for my time intervals:
Time Conversion
0:00 0:00
2:00:00 0.083333333
4:00:00 0.166666667
6:00:00 0.25
8:00:00 0.333333333
10:00:00 0.416666667
12:00:00 0.5
14:00:00 0.583333333
16:00:00 0.666666667
18:00:00 0.75
20:00:00 0.833333333
22:00:00 0.916666667
The Time column is formatted with the time format of 13:13:13.
The Conversion column equals the Time column but is formatted General.
Then I selected the Conversion column, clicked Copy, then Edit - Paste Special and checked Values. This replaces the formula (=cellTime1) in the Conversion column with the actual values.
I then create a lookup table with the conversion column:
Time Time Interval Translation
0:00 a 0 - 1:59 am
0.083333333 b 2 - 3:59 am
0.166666667 c 4 - 5:50 am
0.25 d 6 - 7:59 am
0.333333333 e 8 - 9:59 am
0.416666667 f 10 - 11:59 am
0.5 g 12 - 13:59 pm
0.583333333 h 14 - 15:59 pm
0.666666667 I 16 - 17:59 pm
0.75 j 18 - 19:59 pm
0.833333333 k 20 - 21:59 pm
0.916666667 l 22 or >
The Translation column is for informational purposes only. I called the range from 0:00 to l "timerang".
I then created my column of events and their times and place the formula =VLOOKUP(A3,timerang,2) in the column next to it. This formula is copied through the column. It takes the time you typed in, looks in the range, and returns the range the time is in. You can then use a CountIf function to count the occurances of the events in each range. eg. count how many a's, b's, c's, etc.
Time Interval
2:34:00 b
0:15:00 a
1:45:00 a
3:53:00 b
4:32:00 c
5:23:00 c
6:34:00 d
7:12:00 d
8:20:00 e
9:44:00 e
10:21:00 f
11:23:00 f
12:43:00 g
13:32:00 g
14:54:00 h
15:22:00 h
16:22:00 I
17:43:00 I
18:23:00 j
If anyone knows a better way, please post.
Neil
What I did was this: First I found the internal general number Excel keeps for my time intervals:
Time Conversion
0:00 0:00
2:00:00 0.083333333
4:00:00 0.166666667
6:00:00 0.25
8:00:00 0.333333333
10:00:00 0.416666667
12:00:00 0.5
14:00:00 0.583333333
16:00:00 0.666666667
18:00:00 0.75
20:00:00 0.833333333
22:00:00 0.916666667
The Time column is formatted with the time format of 13:13:13.
The Conversion column equals the Time column but is formatted General.
Then I selected the Conversion column, clicked Copy, then Edit - Paste Special and checked Values. This replaces the formula (=cellTime1) in the Conversion column with the actual values.
I then create a lookup table with the conversion column:
Time Time Interval Translation
0:00 a 0 - 1:59 am
0.083333333 b 2 - 3:59 am
0.166666667 c 4 - 5:50 am
0.25 d 6 - 7:59 am
0.333333333 e 8 - 9:59 am
0.416666667 f 10 - 11:59 am
0.5 g 12 - 13:59 pm
0.583333333 h 14 - 15:59 pm
0.666666667 I 16 - 17:59 pm
0.75 j 18 - 19:59 pm
0.833333333 k 20 - 21:59 pm
0.916666667 l 22 or >
The Translation column is for informational purposes only. I called the range from 0:00 to l "timerang".
I then created my column of events and their times and place the formula =VLOOKUP(A3,timerang,2) in the column next to it. This formula is copied through the column. It takes the time you typed in, looks in the range, and returns the range the time is in. You can then use a CountIf function to count the occurances of the events in each range. eg. count how many a's, b's, c's, etc.
Time Interval
2:34:00 b
0:15:00 a
1:45:00 a
3:53:00 b
4:32:00 c
5:23:00 c
6:34:00 d
7:12:00 d
8:20:00 e
9:44:00 e
10:21:00 f
11:23:00 f
12:43:00 g
13:32:00 g
14:54:00 h
15:22:00 h
16:22:00 I
17:43:00 I
18:23:00 j
If anyone knows a better way, please post.
Neil