Frequency of time range in two columns?
 

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


--
Tone1972
------------------------------------------------------------------------
Tone1972's Profile: http://www.excelforum.com/member.php...o&userid=33316
View this thread: http://www.excelforum.com/showthread...hreadid=531400

Frequency of time range in two columns?
 

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.

--
Cheers
Nigel



"Tone1972" wrote in
message ...

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


--
Tone1972
------------------------------------------------------------------------
Tone1972's Profile:
http://www.excelforum.com/member.php...o&userid=33316
View this thread: http://www.excelforum.com/showthread...hreadid=531400

Frequency of time range in two columns?
 

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

Frequency of time range in two columns?
 

Thanks guys for you advice

I will try those approaches out and see how I go

Cheers

Ton

--
Tone197
-----------------------------------------------------------------------
Tone1972's Profile: http://www.excelforum.com/member.php...fo&userid=3331
View this thread: http://www.excelforum.com/showthread.php?threadid=53140