VLOOKUP (or Equivelent) based on TIME (HH:MM)

[KSingh]

Hi all,

I hope you can help me with the following;

I need a formula that will look at staff Headcount, and start and finish times, and based on that.... fill out a Headcount requirement by hours. (taking the highest headcount within that hour)

So, the source of the data looks like this:

STAFF	START TIME	FINISH
13	6:00	6:44
13	6:59	7:58
13	8:13	8:57
13	9:12	10:47
5	11:17	11:17
15	11:17	11:32
14	11:47	12:59
12	13:14	14:11
14	14:26	14:59

<tbody>
</tbody><colgroup><col><col span="2"></colgroup>

and I need a formula system that would give me a result like the below:

TIME	STAFF
6:00	13
7:00	13
8:00	13
9:00	13
10:00	13
11:00	15
12:00	14
13:00	12
14:00	14
15:00	0
16:00	0
17:00	0
18:00	0

<tbody>
</tbody><colgroup><col><col></colgroup>

Kind Regards,

Kully Singh

 

[Special-K99]

This seems to work

=MAX(IF((HOUR(E1)>=HOUR(B$1:B$10))*(HOUR(E1)<=HOUR(C$1:C$10)),A$1:A$10))
Array formula, use Ctrl-Shift-Enter
and copy down the column

where column E are your hourly times

 

[KSingh]

Hey thanks for your reply but this doesnt seem to work for me.

I have column E as my hourly column

Column A is staff headcount
Column B is my start time
Column C is my finish time.

Its giving me a #Value result

 

[KSingh]

Sorry my fault. I was including column titles in the formula. It works great. Thank you so much for your guidance :D