Month's weekly work shift

[stoure]

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>

 

[WarfritLive]

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]

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!

 

[WarfritLive]

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]

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>

</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]

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

 

[stoure]

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!