Frequency of time range in two columns?

[Tone1972]

Can anyone help me with this?
I have a time range in two columns - a 'from time' in Col A and a 'to
time' in Col B.

I want to work out the most frequent times of occurence.

eg
row one from 8am to 4 pm
row two from 10am to 6 pm
row three from 2pm to 8 pm

what is the most frequently occuring hour?

eg
8am = 1
9am = 1
10am = 2
11am = 2
12pm = 2
1pm = 2
2pm = 3
3pm = 3
4pm =3
5pm = 2 etc

Any ideas?? Cheers Tone

 

[Nigel]

Hi
The HOUR function will return the value 0 to 23 for the time. So if you
apply

HOUR(A1) would reveal the start hour.HOUR(B1)-HOUR(A1) this reveals the gap
in hours.
e.g. 8am 2pm would give 8 and 6

Knowing these values will allow you to populate an array containing each
hour frequency bin.

Pseudo code

Dim HourBins(24)
Scan all rows with date
Extract start hour and ending hour
Update array from start hour to number of hours between
Get Next Row
Print out array

Sorry not enough time to write the actual code, if you need it let me know.

 

[Ken Johnson]

Hi Tone1972,
This isn't a VBA solution, however....

if column C is formatted h:mm AM/PM then C1 = 12:00 AM, C2 has the
formula =C1 + 1/24. Then, if that formula is filled down so that column
C looks like...

12:00 AM
1:00 AM
2:00 AM
3:00 AM
4:00 AM
5:00 AM
6:00 AM
7:00 AM
8:00 AM
9:00 AM
10:00 AM
11:00 AM
12:00 PM
1:00 PM
2:00 PM
3:00 PM
4:00 PM
5:00 PM
6:00 PM
7:00 PM
8:00 PM
9:00 PM
10:00 PM
11:00 PM

Then, if the column D cell next to 12:00 AM in column C has the Formula
=SUMPRODUCT((C1>=$A$1:$A$3)*(C1<=$B$1:$B$3)), which is then filled down
to be level with the column C cell showing 11:00 PM, this formula
reproduces the values you stated.
You then only need to use the MAX function (or similar) to find the
most frequent hour in column D.
The $A$1:$A$3 and $B$1:$B$3 of course will have to be adjusted to suit
you entire range of from and to times.

Ken Johnson

 

[Tone1972]

Thanks guys for you advice

I will try those approaches out and see how I go

Cheers

Ton