Duty Roster_NEED HELP

[Vishal Srivastava]

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 highlighted the area where I need formula to automatically fill the duty i.e. P8,P12, or P16.


I am attaching my working file for your reference.


Thanks in Advance,

Vishal Srivastava

 

[Mike H..]

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 highlighted the area where I need formula to automatically fill the duty i.e. P8,P12, or P16.


I am attaching my working file for your reference.


Thanks in Advance,

Vishal Srivastava

Hi,

I don't follow the P16 bit but is this close?

 

[Ratan Bhushan]

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 highlighted the area where I need formula to automatically fill the duty i.e. P8,P12, or P16.


I am attaching my working file for your reference.


Thanks in Advance,

Vishal Srivastava

Please check if this is you required

 

[Vishal Srivastava]

Hi,

I don't follow the P16 bit but is this close?

Hi Mike,
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

 

[Vishal Srivastava]

Please check if this is you required

Thanks Ratan for your response.

Again I think I didn't explained my problem properly.

I have explained my problem again in MIKE's reply.

Please check and do the needful.

Thanks in Advance
Vishal Srivastava

 

[Ratan Bhushan]

Thanks Ratan for your response.

Again I think I didn't explained my problem properly.

I have explained my problem again in MIKE's reply.

Please check and do the needful.

Thanks in Advance
Vishal Srivastava

hey Vishal

there are two normal shifts
one of 8 hours and one of 12 hours

if I am doing 8 hours shift and decides to do OT for 4 hours, then my timing of shift will be shown as 9 am to 9 pm. If I take the difference between entry time and exit time it is 12 hours, then, how will I differ it is not a normal shift of 12 hours but 4 hours include of OT. there must be some base for the same or there must be a roster for the shifts decided for the employes so that it can be matched if an employee adhering the timings of the shifts. If they across the timings, then, it can be decided what to do
Please reply

 

[Vishal Srivastava]

hey Vishal

there are two normal shifts
one of 8 hours and one of 12 hours

if I am doing 8 hours shift and decides to do OT for 4 hours, then my timing of shift will be shown as 9 am to 9 pm. If I take the difference between entry time and exit time it is 12 hours, then, how will I differ it is not a normal shift of 12 hours but 4 hours include of OT. there must be some base for the same or there must be a roster for the shifts decided for the employes so that it can be matched if an employee adhering the timings of the shifts. If they across the timings, then, it can be decided what to do
Please reply

Hi Ratan,

Staff works in different location ,some location have 8hrs normal shift and some have 12 hrs normal shift.

But 12 hrs are rare one and not an issue if we were able to handle 8hrs shift (So forgot 12 hrs, I could be tackle later).

All individual shifts are independent overtime will not counted as simultaneously.

Suppose you opt of overtime then 4 hrs (rare) or 8 hrs (Mostly) then this should be treated as IInd shift. it means if 1st shift is of
6:00 AM To 2:00 PM


then OT would be recorded separately i.e.

2:00 PM to 6:00 PM
or
2:00 PM to 10:00 PM

It is not overlapping with 1st shift.

Here would be the data:

Input Output
1st Shift 6:00 AM to 2:00PM: P8
2nd Shift 2:00 PM to 6:00PM: P12
or
2nd Shift 2:00 PM to 10:00 PM: P16


Hope you understand my problem.

 

[Ratan Bhushan]

Hi Ratan,

Staff works in different location ,some location have 8hrs normal shift and some have 12 hrs normal shift.

But 12 hrs are rare one and not an issue if we were able to handle 8hrs shift (So forgot 12 hrs, I could be tackle later).

All individual shifts are independent overtime will not counted as simultaneously.

Suppose you opt of overtime then 4 hrs (rare) or 8 hrs (Mostly) then this should be treated as IInd shift. it means if 1st shift is of
6:00 AM To 2:00 PM


then OT would be recorded separately i.e.

2:00 PM to 6:00 PM
or
2:00 PM to 10:00 PM

It is not overlapping with 1st shift.

Here would be the data:

Input Output
1st Shift 6:00 AM to 2:00PM: P8
2nd Shift 2:00 PM to 6:00PM: P12
or
2nd Shift 2:00 PM to 10:00 PM: P16


Hope you understand my problem.

Please check file, I hope this resolves, else not, reply where it is lacking

 

[Vishal Srivastava]

Please check file, I hope this resolves, else not, reply where it is lacking

Hi Ratan,
I think you have not understand my problem.

The formula you have applied is based on single shift only and it is not reference with other shifts.

e.g:
Date Post Code Shift Service No P8 P12 P16
10/09/2014 1 9:00 AM To 5:00 PM 2030 6:00 AM 2:00 PM P8
10/09/2014 2 6:00 AM To 2:00 PM 2030 2:00 PM 10:00 PM P8


if service no 2030 works continuously as stated above, then your formula gives P8 to second shift, Indeed It should be P8 for 1st shift and P16 for second shift.


You formula doesn't have any reference with Service no and date.

Any help really appreciated.

 

[Vishal Srivastava]

Hi Ninjas,
Where are you?
I thought, I would got any solution for my problem but I am still waiting.

I think it is my hard luck that whenever I post my question, I got no answers.

but I am glad that I got solutions for all my old questions after extensive R&D.

Nevertheless, I would work hard and will got the solution for this problem too. As It is my opinion that nothing is impossible in Excel.

I am very big fan of Chandoo and I will be..

But I would again urge to all ninjas to give some heed to my problems also.