How to calculate number of staffs working based on rostered shift timing

[leonardtan]

Shift roster



Using roster attach to count number of staff working in within the range of hour to calculate productivity

 

[Chihiro]

Something like below?
=SUM(IF(IFERROR(LEFT('22Jan_18Feb_PC'!$H$7:$H$35,5)*1<=Summary!A18,0)*IFERROR(RIGHT('22Jan_18Feb_PC'!$H$7:$H$35,5)*1,0)>A18,1,0))

Confirmed as array (CSE)

 

[leonardtan]

Tried the formula but does not work.

Problem 1
For example if Staff A works from 12pm till 10pm 10 hours shift it will be indicated like this on the 27th Jan

and if Staff B Arrives at 1pm till 11pm then from the period of time there will be 2 as listed in the simple scenario

But Staff A is off shift leaving Staff B alone at 11pm

FriSatSunMonTueWedThu
Period27-Jan28-Jan29-Jan30-Jan31-Jan01-Feb02-Feb
12:00 AM0
1:00 AM0
2:00 AM0
3:00 AM0
4:00 AM0
5:00 AM0
6:00 AM0
7:00 AM0
8:00 AM0
9:00 AM0
10:00 AM0
11:00 AM0
12:00 PM1
1:00 PM2
2:00 PM2
3:00 PM2
4:00 PM2
5:00 PM2
6:00 PM2
7:00 PM2
8:00 PM2
9:00 PM2
10:00 PM2
11:00 PM1

___________________________________________________________
Problem 2
If staff A arrives for work on 28th Jan at 11:30am till 9:30pm we will consider him as rounded off to the next whole number which is 12:00pm as listed below and off shift at 9:00pm

Then comes another staff working from 8:30pm till 1:00am

FriSatSunMonTueWedThu
Period27-Jan28-Jan29-Jan30-Jan31-Jan01-Feb02-Feb
12:00 AM01
1:00 AM01
2:00 AM0
3:00 AM0
4:00 AM0
5:00 AM0
6:00 AM0
7:00 AM0
8:00 AM0
9:00 AM0
10:00 AM0
11:00 AM0
12:00 PM11
1:00 PM11
2:00 PM11
3:00 PM11
4:00 PM11
5:00 PM11
6:00 PM11
7:00 PM11
8:00 PM11
9:00 PM12
10:00 PM11
11:00 PM01

 

[Chihiro]

Always upload data that represents your actual data and set up.

What does midnight shift indicate (Ex: 20:00-06:00)? Mar 1st going into Mar 2nd?

Your current data setup is not conducive to analysis. I'd suggest restructuring your data to flat table format to make subsequent analysis and reporting much easier.

 

[leonardtan]

took note on the first point for actual data

yes midnight shift adds up to the following day as in 1st mar to 2nd mar after 12am

do you have such a template for counting number of hours work and productivity in terms of manpower analysis.

 

[Chihiro]

Few questions:
1. Whats the date range for the report? (Typically, I report on work hours etc on bi-weekly basis. Based on Pay Period)

2. What do you define as productivity?

3. Do you consider skill set differences by position? Or just number staffed?

Also, version of Excel with license SKU. That will dictate what sort of tools are available at your disposal.

 

[leonardtan]

Whats the date range for the report? (Typically, I report on work hours etc on bi-weekly basis. Based on Pay Period)
- Based on pay period month to month basis as this ties in with the profit and loss statement on payroll related expenses

2. What do you define as productivity?
- number of staffs work
- number of hours worked for casual workers and part timers
- Number of staffs worked on a weekdays and weekends
- Number of staffs worked on a weekdays and weekends based on hours
- After which we will divide by the number of covers served

3. Do you consider skill set differences by position? Or just number staffed?
- Just number staffed

- I uploaded another file which will provide a better picture.

Thanks for sharing


Also, version of Excel with license SKU. That will dictate what sort of tools are available at your disposal.
- Too bad my company does not invest in this and I'm not using Microsoft office pro version

 

[Chihiro]

Not sure what the new attached file represents.

Are you opposed to changing data structure to flat table for the attachment from post #3?

 

[leonardtan]

yes

 

[leonardtan]

I have revised the template but encounter the following issues.
I am using the Gantt Charting method to calculate productivity.

For example if the staff is rostered 3pm to 1am the following day the formula does not highlight 12am

Formula used is =IF(AND(H$4>=$D6,H$4<=$F6),"1","").

Could anyone assist