Times question

  • Thread starter Thread starter Cesar Zapata
  • Start date Start date
C

Cesar Zapata

,

I 'm making an schedule and I would like know if this is possible.

I'm trying to find out how many ppl are working on the hour.

for example how many ppl are working at 7:00 am or how many ppl are working
at 2:pm. I'm trying to make a chart but I dont know how.



this is how the schedule is set up

A | B | C | D | E | F|G|
| Monday | TUESDAY |
Kat 6:00 AM| 2:00 PM | 6:00 AM|2:00 PM |
Charles 7:00 AM| 3:00 PM| 7:00 AM|3:00 PM |



thanks


cesar
 
Cesar,

Put your times in M2, and down (6 AM, 7 AM, etc). Put in the first two,
select them, then use the fill handle to fill them down quickly.

Put this in N2

=SUMPRODUCT(($B$2:$B$3<=M2)*($C$2:$C$3>=M2))+SUMPRODUCT(($D$2:$D$3<=M2)*($E$
2:$E$3>=M2))...

This presumes Kat's start time in B2. This is for Monday and Tuesday;
you'll have to add three more SUMPRODUCTs for Wed-Fri.

You'll have to expand the range (B2:B3) for the actual count of rows you'll
have. If you need to add rows, move the last row down (select it, then
border-drag it). That will adjust the formula for more rows. Or make it go
to the bottom of the worksheet in the first place($B$2:$B$65536 etc.)

Now you have the times in a column (M), and the counts of on-site persons
for each hour in the next column (N), ready for a chart. It's a miracle!


Earl Kiosterud
mvpearl omitthisword at verizon period net
 
really nice! just what I was looking. I would never be able to figure it out
using that function.

If you were so kind to explain what that function does will be awsome.


Thanks!
 
Back
Top