Thank you Daddy it shows very clear thinking.

Instinctively I thought that if someone started or finished working at a
certain time then they should be shown as working at that time. However,
after digesting your formula and adding to your formula to achieve this I
found that adding up the number of people working and multiplying by 1/2 hr,
your formula gave the correct answer whereas my modification added a hour
per person. (Ok its obvious even to me now! <g )

Well done.

--
Regards

Sandy
In Perth, the ancient capital of Scotland


with @tiscali.co.uk


"daddylonglegs"
wrote in message
news:daddylonglegs.273rba_1146442803.0798@excelfor um-nospam.com...

Sandy Mann Wrote:
With Start Times in Sheet1 A2:M2, End Times in Sheet1 A3:M3 and with
times
in Sheet2 A2:A50 try:

=SUMPRODUCT((Sheet1!$A$2:$M$2<=A2)*(Sheet1!$A$3:$M $3=A2))

But if your wife works in a 24 hour supermarket then you will have
trouble
with midnight so I suggest that you enter midnight times as 0:01 and
23:59


Hi Sandy

Possibly not pertinent here, given SantaUK's comments about days only
working but if there were shifts like 22:00 to 06:00, only times shown,
not dates, then your formula will give incorrect results, I suggest

=SUMPRODUCT(--((Sheet1!$A$2:$M$2<A2)+(Sheet1!$A$3:$M$3A2)+(Shee t1!$A$2:$M$2Sheet1!$A$3:$M$3)=2))


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile:
http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=537622