Hello!
I am trying to find the total hourly headcount for my staffing schedule (see attached) - I would like to the formula to be dynamic so I can see the headcount by day of week (and also needs to account for overnight staff). Any help is greatly appreciated!!Dynamic Hourly Employee Headcount (including overnight staff).xlsx
Your question is ambiguous. When you list 00:00.... do you want the number present AT 00:00 or the number at ANY TIME between 00:00 and 01:00???
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU
Monday in column B MUST be spelled "Monday" and not "Monday ". Try:
Formula:
=SUMPRODUCT(
(
($E$3:$E$51<J$2+TIME(1,0,0))*($F$3:$F$51>J$2)+
(($E$3:$E$51<J$2+TIME(1,0,0))+($F$3:$F$51>J$2))
*($F$3:$F$51<$E$3:$E$51)*($B$3:$B$51=$J$1))
*($E$3:$E$51<>"")*($B$3:$B$51=$J$1)*($C$3:$C$51=$I3))
Hi Glenn, Thanks! But it doesn't seem to be capturing overnight employees - yes, looking for the number present at 0:00.
Be specific. What is not being captured? Where?
No reply, so ONE guess. Using Excel 2019, I had to create 8 Named Ranges (CTRL-F3 to view/edit). These are all dynamic, and will auto-adjust to however many rows you have. Just make sure columns B & C finish on the same row.
Then one final formula, copied across and down:
=SUMPRODUCT(((Start_A<=J$2)*(End_Int>=J$2)*(Day_A=$I$2)+(Start_B<=J$2)*(End_B>=J$2)*(Day_B=$I$2))*(Role=$I3)*(Start_A<>""))
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks