# Month's weekly work shift

#### stoure

##### New Member
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 ...

 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>

### Excel Facts

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.

#### WarfritLive

##### Board Regular
Hello stoure,

Just wanted to be clear on the rules. For example, my first thought is just to look at the shift start times. 00, 06, 13, 18 and 20. If they work starting at 06, it looks like they usually return three shifts later at 20. But then they always get an entire day off and return at 13?

Based on your table it seems Worker's 1&4 will always have the same schedule, as will 2&5. 3 remains independent. Am I correct in assuming you just need to carry this chart onwards into the future, or would you prefer to set the first shift for each worker and have the rest of the week fill itself in?

#### stoure

##### New Member
Hello stoure,

Just wanted to be clear on the rules. For example, my first thought is just to look at the shift start times. 00, 06, 13, 18 and 20. If they work starting at 06, it looks like they usually return three shifts later at 20. But then they always get an entire day off and return at 13?

It's not always the case. In general the begining shift of a worker start normalley at 13 and end at 18(in case of special need till 20), then he'll rest the following shift(from 20 to 06) and come back to duty from 06 to 13 then rest from 13 to 20 and re-work again from 20 to 06. after that he'll rest the whole day and start a new shift the next day at 13 and so on....

Based on your table it seems Worker's 1&4 will always have the same schedule, as will 2&5. 3 remains independent. Am I correct in assuming you just need to carry this chart onwards into the future, or would you prefer to set the first shift for each worker and have the rest of the week fill itself in?
Yes you're correct about the shift and workers. And I'd like the rest of the whole month fill itself(a separate table for each week).
I hope I've answered correctly you questions!

Last edited:

#### WarfritLive

##### Board Regular
I see a pattern of 4,3,1, and 7 columns between each shift, repeating. There are three different possible schedules that can always be determined by the first three shifts of the first day.
So if we =MATCH("X",\$B8:\$D8,0) we can determine which of the three schedules a worker is on. For the sake of simplicity, let's say worker 1 is on schedule 3, worker 2 is on schedule 2, and worker 3 is on schedule 1(because that's how the match function returns it).

Looking at the table we can use schedule 2 as a middle point. Schedule 3 is exactly five columns ahead of it and schedule 1 is exactly 5 columns behind. So we can use the formula below to equal them out
=CHOOSE(MATCH("X",\$B8:\$D8,0), -5, 0, 5)

The first column that will need a formula is E. Let's start in E9 because it's our base line (schedule 2)
=COLUMN(E9) gives us our column of 5 so
=COLUMN(E9)+CHOOSE(MATCH("X",\$B8:\$D8,0), -5, 0, 5)
will align them all appropriately. Starting zero. And for schedule 2 we want X's in columns F(column 6), G(7), N(14), R(18), U(21), V(22), etc. So in E9:

=IF(IFERROR(MATCH(COLUMN(E9)+CHOOSE(MATCH("X",\$B8:\$D8,0), -5, 0, 5), {3;6;7;14;18;21;22;29;33}), 0)>0, "X","") should tell us if it it should be a shift or not.

There's a way to calculate that array but I've gotten lazy and just did it manually. Let me know if that works for you

#### stoure

##### New Member
I see a pattern of 4,3,1, and 7 columns between each shift, repeating.
I don't understand what you mean by that sentence above !!

There are three different possible schedules that can always be determined by the first three shifts of the first day.
That's right!

So if we =MATCH("X",\$B8:\$D8,0) we can determine which of the three schedules a worker is on. For the sake of simplicity, let's say worker 1 is on schedule 3, worker 2 is on schedule 2, and worker 3 is on schedule 1(because that's how the match function returns it).
what do b8 and d8 correspond for you ? in my sheet D8 correspond to the end of shift 13 cell(13) and B8 correspond to
 Shift end .

<tbody>
</tbody>

<colgroup><col></colgroup><tbody>
Looking at the table we can use schedule 2 as a middle point. Schedule 3 is exactly five columns ahead of it and schedule 1 is exactly 5 columns behind.
correct too.
So we can use the formula below to equal them out
=CHOOSE(MATCH("X",\$B8:\$D8,0), -5, 0, 5)

The first column that will need a formula is E. Let's start in E9 because it's our base line (schedule 2)
=COLUMN(E9) gives us our column of 5 so
=COLUMN(E9)+CHOOSE(MATCH("X",\$B8:\$D8,0), -5, 0, 5)
will align them all appropriately. Starting zero. And for schedule 2 we want X's in columns F(column 6), G(7), N(14), R(18), U(21), V(22), etc.
understood till here in this quote.
So in E9:
: in my sheet E9 is schedule3(worker1 : monday : 13-18) and it is cross chechek : should I replace the cross "X" by the formula in the cell .
=IF(IFERROR(MATCH(COLUMN(E9)+CHOOSE(MATCH("X",\$B8:\$D8,0), -5, 0, 5), {3;6;7;14;18;21;22;29;33}), 0)>0, "X","") should tell us if it it should be a shift or not.

There's a way to calculate that array but I've gotten lazy and just did it manually. Let me know if that works for you
.
I need some more details to compute it correctly.

I don't know how you've chosen your cells' designation. I'm not sure if it fits with my sheets.
SO I don't understand where to exactely use the formula.
My columns start at B and end at AK.
My lines start from 3 ( Week from 01/12 au 04/12 2016) and end at 13(worker5).
So where sould I compute E9 ? Because at my E9 it empty all the cells...

 Week from 01/12 au 04/12 2016 28 28 28 28 28 29 29 29 29 29 30 30 30 30 30 01 01 01 01 01 02 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

<colgroup><col><col><col><col span="11"><col><col><col span="20"></colgroup><tbody>
</tbody>

#### WarfritLive

##### Board Regular
I was counting cells based on your table in the first post. If your D8 is end of shift 13, I'm one down and one to the right of you. End of shift 13 is cell c7 for me. Use instead :

=IF(IFERROR(MATCH(COLUMN(F10)+CHOOSE(MATCH("X",\$C9:\$E9,0), -5, 0, 5)-1, {3;6;7;14;18;21;22;29;33}), 0)>0, "X","")

Edit: this formula goes in F10. Autofill as appropriate

Last edited:

#### stoure

##### New Member
Morning;
I still got trouble to make it work properly; If I did so only F10 is crossed all the other adjacent cells are filled blank.
Anyway, thanks a lot. If the forum would let us to join our excel file it would be better. I think that finally I'll fill the cells my self(before succeeding with WarfritLive's formula).

Is it possible to calculate the work hours for each worker during the month based on the table per week's dispaly ?

Thanks again!

Replies
13
Views
820
Replies
6
Views
1K
Replies
11
Views
334
Replies
4
Views
327
Replies
3
Views
280

1,195,618
Messages
6,010,730
Members
441,566
Latest member
spimcom

### 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.

### Which adblocker are you using?

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

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