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:

[TABLE="width: 298"]
<tbody>[TR]
[TD]STAFF[/TD]
[TD]START TIME [/TD]
[TD]FINISH[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]6:00[/TD]
[TD]6:44[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]6:59[/TD]
[TD]7:58[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]8:13[/TD]
[TD]8:57[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]9:12[/TD]
[TD]10:47[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]11:17[/TD]
[TD]11:17[/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD]11:17[/TD]
[TD]11:32[/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]11:47[/TD]
[TD]12:59[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]13:14[/TD]
[TD]14:11[/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]14:26[/TD]
[TD]14:59[/TD]
[/TR]
</tbody><colgroup><col><col span="2"></colgroup>[/TABLE]


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

[TABLE="width: 160"]
<tbody>[TR]
[TD]TIME[/TD]
[TD]STAFF[/TD]
[/TR]
[TR]
[TD]6:00[/TD]
[TD]13[/TD]
[/TR]
[TR]
[TD]7:00[/TD]
[TD]13[/TD]
[/TR]
[TR]
[TD]8:00[/TD]
[TD]13[/TD]
[/TR]
[TR]
[TD]9:00[/TD]
[TD]13[/TD]
[/TR]
[TR]
[TD]10:00[/TD]
[TD]13[/TD]
[/TR]
[TR]
[TD]11:00[/TD]
[TD]15[/TD]
[/TR]
[TR]
[TD]12:00[/TD]
[TD]14[/TD]
[/TR]
[TR]
[TD]13:00[/TD]
[TD]12[/TD]
[/TR]
[TR]
[TD]14:00[/TD]
[TD]14[/TD]
[/TR]
[TR]
[TD]15:00[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]16:00[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]17:00[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]18:00[/TD]
[TD]0[/TD]
[/TR]
</tbody><colgroup><col><col></colgroup>[/TABLE]


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