software

My wife has asked me to create a sheet that will give her the following
info.

She works in a store with a number of checkouts. The sheet will have all
her staff start times and finish times. This will be inputted every day.
Excel will then create another sheet that will show the time of the day at
30 min intervals how many checkout staff is on at that time. Nothing too
fancy, I have been sitting here for two hours trying to figure this one out.
I have a screenshot of the info if anyone wants it if I'm not clear enough.

newsgroup @ mmillar . co . uk

delete the spaces to reply!

thanks folks

--
RegardsM Millar

sure send us a screen shot--
davesexcel------------------------------------------------------------------------
davesexcel's Profile: www.excelforum.com/member.php...oamp;userid=31708
View this thread: www.excelforum.com/showthread...hreadid=537622With 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$2lt;=A2)*(Sheet1!$A$3:$M $3gt;=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--
HTH

Sandy
In Perth, the ancient capital of Scotland

with @tiscali.co.ukquot;SantaUKquot; gt; wrote in message
...
gt; My wife has asked me to create a sheet that will give her the following
gt; info.
gt;
gt; She works in a store with a number of checkouts. The sheet will have all
gt; her staff start times and finish times. This will be inputted every day.
gt; Excel will then create another sheet that will show the time of the day at
gt; 30 min intervals how many checkout staff is on at that time. Nothing too
gt; fancy, I have been sitting here for two hours trying to figure this one
gt; out. I have a screenshot of the info if anyone wants it if I'm not clear
gt; enough.
gt;
gt; newsgroup @ mmillar . co . uk
gt;
gt; delete the spaces to reply!
gt;
gt; thanks folks
gt;
gt; --
gt; Regards
gt;
gt;
gt; M Millar
gt;
gt;
Cheers guys - Specially to the Captain, as I've now got it working. And
thanks for the pointer on the 24 hour - though she has just moved out of one
to a quot;days onlyquot; store!

--
RegardsM Millar

quot;SantaUKquot; gt; wrote in message
...
gt; My wife has asked me to create a sheet that will give her the following
gt; info.
gt;
gt; She works in a store with a number of checkouts. The sheet will have all
gt; her staff start times and finish times. This will be inputted every day.
gt; Excel will then create another sheet that will show the time of the day at
gt; 30 min intervals how many checkout staff is on at that time. Nothing too
gt; fancy, I have been sitting here for two hours trying to figure this one
gt; out. I have a screenshot of the info if anyone wants it if I'm not clear
gt; enough.
gt;
gt; newsgroup @ mmillar . co . uk
gt;
gt; delete the spaces to reply!
gt;
gt; thanks folks
gt;
gt; --
gt; Regards
gt;
gt;
gt; M Millar
gt;
gt;

Sandy Mann Wrote:
gt; With Start Times in Sheet1 A2:M2, End Times in Sheet1 A3:M3 and with
gt; times
gt; in Sheet2 A2:A50 try:
gt;
gt; =SUMPRODUCT((Sheet1!$A$2:$M$2lt;=A2)*(Sheet1!$A$3:$M $3gt;=A2))
gt;
gt; But if your wife works in a 24 hour supermarket then you will have
gt; trouble
gt; with midnight so I suggest that you enter midnight times as 0:01 and
gt; 23:59
gt;

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$2lt;A2) (Sheet1!$A$3:$M$3gt;A2) (Shee t1!$A$2:$M$2gt;Sheet1!$A$3:$M$3)=2))--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: www.excelforum.com/member.php...oamp;userid=30486
View this thread: www.excelforum.com/showthread...hreadid=537622Thank 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! lt;ggt; )

Well done.

--
Regards

Sandy
In Perth, the ancient capital of Scotland

with @tiscali.co.ukquot;daddylonglegsquot; gt;
wrote in message
news:daddylonglegs.273rba_1146442803.0798@excelfor um-nospam.com...
gt;
gt; Sandy Mann Wrote:
gt;gt; With Start Times in Sheet1 A2:M2, End Times in Sheet1 A3:M3 and with
gt;gt; times
gt;gt; in Sheet2 A2:A50 try:
gt;gt;
gt;gt; =SUMPRODUCT((Sheet1!$A$2:$M$2lt;=A2)*(Sheet1!$A$3:$M $3gt;=A2))
gt;gt;
gt;gt; But if your wife works in a 24 hour supermarket then you will have
gt;gt; trouble
gt;gt; with midnight so I suggest that you enter midnight times as 0:01 and
gt;gt; 23:59
gt;gt;
gt;
gt; Hi Sandy
gt;
gt; Possibly not pertinent here, given SantaUK's comments about days only
gt; working but if there were shifts like 22:00 to 06:00, only times shown,
gt; not dates, then your formula will give incorrect results, I suggest
gt;
gt; =SUMPRODUCT(--((Sheet1!$A$2:$M$2lt;A2) (Sheet1!$A$3:$M$3gt;A2) (Shee t1!$A$2:$M$2gt;Sheet1!$A$3:$M$3)=2))
gt;
gt;
gt; --
gt; daddylonglegs
gt; ------------------------------------------------------------------------
gt; daddylonglegs's Profile:
gt; www.excelforum.com/member.php...oamp;userid=30486
gt; View this thread: www.excelforum.com/showthread...hreadid=537622
gt;