Duty Roster_Help

vishal0589

Board Regular
Joined
May 13, 2014
Messages
63
Hi friends,
Can you help in this condition?

I am working on a duty roster. I need a formula with following conditions:

Shifts:
6:00 AM To 2:00 PM
2:00 PM To 10:00 PM
10:00 PM To 6:00 AM
9:00 AM To 5:00 PM
8:00 AM To 8:00 PM
8:00 PM To 8:00 AM
and so on.


A worker can work upto 24 hrs a day.

Normal duty could be of 8hrs or 12 Hrs.

If staff works for normal duty(1 shift) i.e. 8 hrs, then duty will be termed as "P8" (e.g: 6:00 AM To 2:00 PM)

If staff opts for overtime (4 Hrs, Fixed) , total duty would be termed as "P12" (Normal+Overtime)

If staff works for two to three shifts continuously (i.e. P8+P8 or P8+P8+P8), then total duty would be termed as "P16"

IF staff works for shift in night in dayone and his continuous duty of second shift falls under second day i.e. 1st Shift 10:00 PM To 6:00 AM (Day-1) and second shift 6:00 AM To 2:00 PM (Day-2), this would not be treated as overtime and would be charged seperately i.e, P8 for 1st shift and P8 for 2nd Shift (NOT P16)


There are 1600 staffs works on 2 to 3 shifts daily. Data have to be entered on daily basis.

I have Bold the area where I need formula to automatically fill the duty i.e. P8,P12, or P16.

Date
Post CodeShiftService NoP8
P12P16
10/09/201419:00 AM To 5:00 PM
2030
10/09/201426:00 AM To 2:00 PM2030
11/09/201432:00 PM To 10:00 PM2284
12/09/2014410:00 PM To 6:00 AM2030
13/09/201459:00 AM To 5:00 PM2030
10/09/201468:00 AM To 8:00 PM2030
10/09/201478:00 PM To 8:00 AM2030
10/09/201489:00 AM To 5:00 PM2284
10/09/201496:00 AM To 2:00 PM2250
10/09/2014102:00 PM To 10:00 PM2030
10/09/20141110:00 PM To 6:00 AM2284

<colgroup><col><col><col><col><col span="3"></colgroup><tbody>
</tbody>




Thanks in Advance,

Vishal Srivastava
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
Vishal,

All of your data reflects 8 hour shifts. These would all be P8. How does the actual data look? Do you have Timestamps in certain columns? Do you add up the difference between timestamps? Maybe something like this:

Excel 2012
ABCDEFG
1DatePost CodeTime 1Time 2DifferenceService NoP Status
210/9/201419:0017:008.002030P8
310/9/201426:0014:008.002030P8
411/9/2014314:0022:008.002284P8
512/9/2014422:0010:0012.002030P12
Sheet2
Cell Formulas
RangeFormula
E2=ABS(D2-C2)*24
E3=ABS(D3-C3)*24
E4=ABS(D4-C4)*24
E5=ABS(D5-C5)*24
G2=IF(E2=8,"P8",IF(E2=12,"P12",IF(E2=16,"P16","N/A")))
G3=IF(E3=8,"P8",IF(E3=12,"P12",IF(E3=16,"P16","N/A")))
G4=IF(E4=8,"P8",IF(E4=12,"P12",IF(E4=16,"P16","N/A")))
G5=IF(E5=8,"P8",IF(E5=12,"P12",IF(E5=16,"P16","N/A")))


The more detailed the information you can provide the better people will be able to assist you.

Columns C and D are in Time Format. Column E is in Number Format
 
Upvote 0
Hi mrmmickle1



Thanks for response,

I think i have not explained my problem properly. Now I am trying again:

--> There are three shifts
--> A person can work three shifts maximum of 24 hrs.
--> Normal Shifts are of 8 hrs or 12 hrs.
--> If staff works for Normal shift and thereafter opts for second shift, then 1st shift would be termed as P8 and second shift will be treated as P16
--> If staff works for Normal shift and thereafter opts for second shift and third shift , then 1st shift would be termed as P8 and second shift will be treated as P16 and third shift will also be treated as P16

--> If gap between shifts is of more than 3 hrs, then:
-> If staff works for Normal shift and thereafter opts for second shift, then 1st shift would be termed as P8 and second shift will be treated as P8
-> If staff works for Normal shift and thereafter opts for second shift and third shift , then 1st shift would be termed as P8 and second shift will be treated as P8 and third shift will also be treated as P8 (Provided gap is more than 3 hrs).

--> If 1st shift falls in 1 day and another shift falls in another day, then 3 hrs gap will not be considered and both shifts will be treated as normal shifts i.e. P8 and P8.

So the formula should be based on date, time as well as Service no.

Waiting for your response.

Thanks in Advance.

Vishal Srivastava
 
Upvote 0
A couple of questions:

Does "Service No" refer to an individual worker? Not sure as your service number "2030" obviously has several overlapping shifts on 10/90/2014 but perhaps this is fictitious data.

Is the "Post Code" relevant to the calculation?

Does the data come in a time ordered list? Your sample list is not date time ordered.

You will obviously need identification of the individual workers to calculate the time gap between shifts and it would help if the data is in date time order by row:

09/10/2014 08:00 to 16:00 (Worker 1)
09/10/2014 08:00 to 20:00 (Worker 2)
09/10/2014 10:00 to 18:00 (Worker 3)
09/10/2014 20:00 to 04:00 (Worker 3) giving gap of 2 hours from last shift
09/10/2014 20:00 to 04:00 (Worker 1) giving gap of 4 hours from last shift
10/10/2014 08:00 to 20:00 (Worker 2) giving gap of 12 hours from last shift
 
Upvote 0
Hi JB_Scotland
Thanks for response.

Here is answer to your questions:
1) Service no. is an unique employee code allotted to single employee only
2) No post code is not relevant for calculation, however it will be used MIS purpose.
3) Data will be recorded daily but shift timing will not be sorted one.(This is real problem)


Hope you have understood my problem but I don't know how make a formula to check on daily basis.

Waiting for your response.
 
Upvote 0
One option may be to add a workbook open event to double sort the data. So when you open the workbook it would do something like Sort Data By Date and Then Sort Data By Employee.... OR like JB_Scotland mentioned maybe sort it by Date, then Time, then Employee. Something like this is not too difficult to accomplish. Would this be feasible vishal0589? This way no person has to really worry about accomplishing this. It can be done automatically when the file opens...

I really think the key to your issue lies with the format you are entering the data in. If you can not get the information you need out of it in its current format then it lies to reason that you may need to change the setup. While change can be difficult, it can oftentimes optimize efficiency.
 
Upvote 0
OK! I got your point. Suppose I have done the sorting as suggested by you and JB_Scotland but my will problem remains the same.

How could I develop a formula to maintain the staff duty in P8,P12 and P16 shifts as per condition specified above?

Can you help me?

DatePost CodeShiftService NoP8P12 P16
10/09/201411:00 AM To 9:00 AM2030
10/09/201419:00 AM To 5:00 PM2030
10/09/2014310:00 PM To 6:00 AM2030
10/09/201446:00 AM To 2:00 PM2284
10/09/201452:00 PM To 10:00 PM2284

<tbody>
</tbody>

If you think the format is not worth enough to handle this situation, then It could be modified. As I have to maintain the format also and It will be used for daily entry (for 1600 employees).

But Conditions specified must be fulfilled.

If You would like to suggest any changes, It will be highly Appreciated.

Waiting for your response.

Thanks in Advance
 
Last edited:
Upvote 0
Vishal,

I'm working on this and have got some ideas.

Would a worker doing two shifts of back to back be recorded as one entry?

One entry:
10/09/14 10:00 to 02:00 (for Service no 2030)

Or two entries:
10/09/14 10:00 to 18:00 (for Service no 2030)
10/09/14 18:00 to 02:00 (for Service no 2030)

Also just want to know if Service Numbers are actually all integer numbers and don't include other characters?

JB
 
Upvote 0
Hi JB,
Thanks for your time. Here is answer to your queries.

1) There would be 2 entries separately.
2) Service no have only integer no and do not include any characters.

Vishal Srivastava
 
Upvote 0
Thanks,

Just to be clear would a 12 hour shift also be recorded as two entries (8 + 4)? In other words: 10:00 to 18:00 and 18:00 to 22:00 as two separate entries or just 10:00 to 22:00?

Also I'm assuming in the P8, P12 and P16 columns you want the actual hours worked in that category.

So according to your rules:

If a worker did 8 + 8 + 8 back to back shifts the P16 column would show three entries of 8 hours, one for each shift line that meets the rules.
If a worker did 8 one day (overnight) straight into another 8 with no break the P8 column would show two entries of 8 hours.
If a worker did 8 + 4 back to back the P12 column would show one entry of 8 hours and one of 4 hours.

JB
 
Upvote 0

Forum statistics

Threads
1,213,551
Messages
6,114,268
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top