Hello here;
I'd like to set a monthly based work shift per week. I have trouble to set the formulas properly so that Excel would compute the dates , the shifts and to perform the total worked hours calculation in the month per worker.
There are 3 shift per for worker(or group of workers) : From 06h to 13h, from 13h to 18 h (eventually from 18h to 20h in case of service need) and from 20h to 06h..
If a worker worked from 06 to 13 he will rest from 13h to 18h and come back from 20 to 06. He will rest afain from 20 to 06 and come back the next dat from 13h to 18h and so on.
This is the case for each worker. If ther are 3 workers they will turn so that at every shift one worker is on duty and the 2 others on rest.
the week start from monday to sunday along the month but only the days in the chosen month are computed as worked hours based.
Below there are the layouts for two weeks of december . I have trouble do the formulas continously ...
<colgroup><col><col><col><col span="11"><col><col><col span="20"></colgroup><tbody>
</tbody>
I'd like to set a monthly based work shift per week. I have trouble to set the formulas properly so that Excel would compute the dates , the shifts and to perform the total worked hours calculation in the month per worker.
There are 3 shift per for worker(or group of workers) : From 06h to 13h, from 13h to 18 h (eventually from 18h to 20h in case of service need) and from 20h to 06h..
If a worker worked from 06 to 13 he will rest from 13h to 18h and come back from 20 to 06. He will rest afain from 20 to 06 and come back the next dat from 13h to 18h and so on.
This is the case for each worker. If ther are 3 workers they will turn so that at every shift one worker is on duty and the 2 others on rest.
the week start from monday to sunday along the month but only the days in the chosen month are computed as worked hours based.
Below there are the layouts for two weeks of december . I have trouble do the formulas continously ...
Week from 01/12 au 04/12 2016 | |||||||||||||||||||||||||||||||||||
28 | 28 | 28 | 28 | 28 | 29 | 29 | 29 | 29 | 29 | 30 | 30 | 30 | 30 | 30 | 30 | 01 | 01 | 01 | 01 | 01 | 02 | 02 | 02 | 02 | 03 | 03 | 03 | 03 | 03 | 04 | 04 | 04 | 04 | 04 | |
N | D | D | D | N | N | D | D | D | N | N | D | D | D | N | N | D | D | D | N | N | D | D | D | N | N | D | D | D | N | N | D | D | D | N | |
lundi 28 | mardi 29 | mercredi 30 | jeudi 01 | vendredi 02 | samedi 03 | dimanche 04 | |||||||||||||||||||||||||||||
Shift begining | 00 | 06 | 13 | 18 | 20 | 00 | 06 | 13 | 18 | 20 | 00 | 06 | 13 | 18 | 20 | 00 | 06 | 13 | 18 | 20 | 00 | 06 | 13 | 18 | 20 | 00 | 06 | 13 | 18 | 20 | 00 | 06 | 13 | 18 | 20 |
Shift end | 06 | 13 | 18 | 20 | 00 | 06 | 13 | 18 | 20 | 00 | 06 | 13 | 18 | 20 | 00 | 06 | 13 | 18 | 20 | 00 | 06 | 13 | 18 | 20 | 00 | 06 | 13 | 18 | 20 | 00 | 06 | 13 | 18 | 20 | 00 |
Worker 1 | X | X | X | X | X | X | X | X | X | ||||||||||||||||||||||||||
Worker 2 | X | X | X | X | X | X | X | X | X | X | |||||||||||||||||||||||||
Worker 3 | X | X | X | X | X | X | X | X | X | ||||||||||||||||||||||||||
Worker 4 | X | X | X | X | X | X | X | X | X | ||||||||||||||||||||||||||
Worker 5 | X | X | X | X | X | X | X | X | X | X | |||||||||||||||||||||||||
Week from 05/12 au 11/12 2016 | |||||||||||||||||||||||||||||||||||
05 | 05 | 05 | 05 | 05 | 06 | 06 | 06 | 06 | 06 | 07 | 07 | 07 | 07 | 07 | 07 | 08 | 08 | 08 | 08 | 08 | 09 | 09 | 09 | 09 | 10 | 10 | 10 | 10 | 10 | 11 | 11 | 11 | 11 | 11 | |
N | D | D | D | N | N | D | D | D | N | N | D | D | D | N | N | D | D | D | N | N | D | D | D | N | N | D | D | D | N | N | D | D | D | N | |
lundi 05 | mardi 06 | mercredi 07 | jeudi 08 | vendredi 09 | samedi 10 | dimanche 11 | |||||||||||||||||||||||||||||
Shift begining | 00 | 06 | 13 | 18 | 20 | 00 | 06 | 13 | 18 | 20 | 00 | 06 | 13 | 18 | 20 | 00 | 06 | 13 | 18 | 20 | 00 | 06 | 13 | 18 | 20 | 00 | 06 | 13 | 18 | 20 | 00 | 06 | 13 | 18 | 20 |
Shift end | 06 | 13 | 18 | 20 | 00 | 06 | 13 | 18 | 20 | 00 | 06 | 13 | 18 | 20 | 00 | 06 | 13 | 18 | 20 | 00 | 06 | 13 | 18 | 20 | 00 | 06 | 13 | 18 | 20 | 00 | 06 | 13 | 18 | 20 | 00 |
Worker 1 | X | X | X | X | X | X | X | X | X | X | |||||||||||||||||||||||||
Worker 2 | X | X | X | X | X | X | X | X | X | ||||||||||||||||||||||||||
Worker 3 | X | X | X | X | X | X | X | X | X | ||||||||||||||||||||||||||
Worker 4 | X | X | X | X | X | X | X | X | X | X | |||||||||||||||||||||||||
Worker 5 | X | X | X | X | X | X | X | X | X | ||||||||||||||||||||||||||
<colgroup><col><col><col><col span="11"><col><col><col span="20"></colgroup><tbody>
</tbody>