• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Using an Array Formula to Show Staffing Distribution

E

EmmaFT

New Member
Hi Chandoo Community,

I am trying to develop an array formula to analyze timesheet data.

Employee ID - Column A
Shift Start Time - Column B
Shift End Time- Column C
Shift Start Date - Column D
Shift End Date - Column E

I have several thousand records and I am trying to find a way to see how many people are on duty at any given time. For instance I want to know how many people have a shift that covers 14:00:00 -16:00:00 on Wednesday. What is the best way to do this? I was trying to work with =SUMPODUCT and having trouble.

Thank you!
 
Here is an example file! Thank you for your help! I want to be able too see overlap and how many people are working at any given time.
 

Attachments

I don't know the best way to figure it out. I was trying several variations on =COUNTIF and =SUMPRODUCT but couldn't get anything to work. I want the output to be the number of people whose shift covers a one or two hour interval. For instance on Mondays between 11:00 -13:00 two people (scott and Ray) have a shift.
 
@NARAYANK991 Sir,

1. I assume you had assume the shift hours to be fixed 8 hours.
2. What if somebody worked more than 8 hours?
3. Your formula showing for some one who was partial present in the criteria hours say for example Day Monday, From Time: 13:00 To time 17:00, it shows Scott, Lisa & Ray on duty. But if you see Lisa joined duty after 2 hours from FROM time & Ray left duty 1 hour before TO time. So if some incident occur in their absence time it will show On duty and unneccesary they will be caught ;) :).

If I change your formula to the one below i.e. include End time than I think it might give more precise result.
=IF(C2=LEFT($K$1,3),IF(AND($N$1>=D2,$P$1<=D2+"8:0:00"),"On Duty",""),"")

Kindly, express your thought and pardon me If I am wrong somewhere in my understanding.

Regards,
 
You must log in or register to reply here.
Back
Top