# Help on shift management

#### stoure

##### New Member
Morning to everyone,
I have some trouble to process the worked time of different workers in a month.
Here you have a sample below :
 Week from 12/01 to 12/03 2016 dimanche 27 lundi 28 mardi 29 mercredi 30 jeudi 01 vendredi 02 samedi 03 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

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

I'd like to process the number of hours worked per worker and per date.
I'd like to take into account night hours(from 21 to 06) and holidays(such as 01/01/2017).

### Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

#### jorismoerings

##### Well-known Member
Hi and welcome to MrExcel,

Wow! Your question is open to so much assumptions any answer given will probably not the one you're looking for.

Can you describe in more detail what your question is.
I'd like to process the number of hours worked per worker and per date.
Do you mean the sum of hours worked per date?
I'd like to take into account night hours(from 21 to 06)
Is the assumption that any worker who worked the shift from 20 to 00 and the shift from 00 to 06 is eligible for this question?
I'd like to take into account holidays(such as 01/01/2017)
What do you mean? Do you not want ot count the hours worked if it's a holiday or .....

Perhaps alo good to read this http://www.mrexcel.com/forum/board-announcements/127080-guidelines-forum-use.html

#### stoure

##### New Member
"Can you describe in more detail what your question is : "
See the table below what I'd like for each agent :
 Date 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 Total Worked hours Night hours Day off hours

<tbody>
</tbody>

There are 3 shifts (06-13; 13-18 and 20-06) : 20-00 and 00-06 are linked. 18-20 is per time(in case of need) and it is linked with 13-18 if performed.

I hope It is clearer now.

#### jorismoerings

##### Well-known Member
Hi,

I've seen some merged cells in your first post and merged cells are a superb solution for creating visual effect but are a true nightmare if they need to be inserted in formula's.
So if you can; get rid them of them!

Your explanation shows some kind of reporting grid but doesn't answer any of my questions but i'll give it a go.
To do so i've slightly changed your lay outs as will be shown below.

Try changing your roster grid to this:

Excel 2016 (Windows) 64 bit
1Week from 12/01 to 12/03 2016
2
327-12-201628-12-201629-12-201630-12-201631-12-20161-1-20172-1-2017
4272727272728282828282929292929303030303031313131311111122222
5NDDDNNDDDNNDDDNNDDDNNDDDNNDDDNNDDDN
6Shift begining06131820061318200613182006131820061318200613182006131820
7Shift end613182024613182024613182024613182024613182024613182024613182024
8Worker 1XXXXXXXXX
9Worker 2XXXXXXXXXX
10Worker 3XXXXXXXX
Sheet1
Cell Formulas
RangeFormula
B4=B3
B5=IF(OR(B6=0,B6=20),"N","D")

as you'll notice i've added 2 auxiliary ranges to solve the merged cells issue. Hide them or group them if you want the same visual as you've had before.
From this roster i couldn't determine if there were days off. so ...

Then for the reporting grid:

Excel 2016 (Windows) 64 bit
1december
2Worker 1
3Date01020304050607080910111213141516171819202122232425262728293031Total
4Worked hours0000000000000000000000000065116533
5Night hours000000000000000000000000006046016
6Day off hours
Sheet2
Cell Formulas
RangeFormula
B4=SUMPRODUCT((Sheet1!\$B\$4:\$AJ\$4=B\$3)*(Sheet1!\$A\$8:\$A\$10=\$A2)*(Sheet1!\$B8:\$AJ10="X")*((Sheet1!\$B\$7:\$AJ\$7)-(Sheet1!\$B\$6:\$AJ\$6)))
B5=SUMPRODUCT((Sheet1!\$B\$4:\$AJ\$4=B\$3)*(Sheet1!\$A\$8:\$A\$10=\$A2)*(Sheet1!\$B\$5:\$AJ\$5="N")*(Sheet1!\$B8:\$AJ10="X")*((Sheet1!\$B\$7:\$AJ\$7)-(Sheet1!\$B\$6:\$AJ\$6)))

Last edited:

#### stoure

##### New Member
Thanks a lot jorismoerings,
I am very glab to read your answer once again. this one is going to help me a lot.
by away, Let me try to respond to your questions may be it can help.
"I'd like to process the number of hours worked per worker and per date."

I mean by that sentences : In a given month (let take December 2016) I'll need to plot all the weekly based work shift of December(week start from Monday to Sunday but we'll take into account only december date for shift crossed check and for hours calculation). We may have 4 or 5 weeks depending on the month we chose.
We'll need another sheet to process the work hours in the given month for each worker.

I'd like to take into account night hours(from 21 to 06)

I mean that when calculating the number of hours performed by a worker in a month , we need to process also the total work hours that happens by night(night hour start at 21 and end at 6 - but night shift is from 20 to 06). And night hours is included in the work hours of a worker.

I'd like to take into account holidays(such as 01/01/2017)

I mean if I can create a sheet on which I will plot the list worker and their holidays date : I'd like the system to uncheck them from the weekly shift at that moment and obviously they won't have worked hour at that dates.

--- In add I'd like to take into account day off our(by creating the list of the days which are off so that the work hours at that date will be plot a part out of the normal work hours.

Hope I'm clearer now.

Is it possible that you sent me the file you performed so that I can adapt them to my need ? my mail is : maliwarialtyahoo.fr

#### stoure

##### New Member
Hi,

I've seen some merged cells in your first post and merged cells are a superb solution for creating visual effect but are a true nightmare if they need to be inserted in formula's.
So if you can; get rid them of them!

Your explanation shows some kind of reporting grid but doesn't answer any of my questions but i'll give it a go.
To do so i've slightly changed your lay outs as will be shown below.

Try changing your roster grid to this:
Excel 2016 (Windows) 64 bit
1Week from 12/01 to 12/03 2016
2
327-12-201628-12-201629-12-201630-12-201631-12-20161-1-20172-1-2017
4272727272728282828282929292929303030303031313131311111122222
5NDDDNNDDDNNDDDNNDDDNNDDDNNDDDNNDDDN
6Shift begining06131820061318200613182006131820061318200613182006131820
7Shift end613182024613182024613182024613182024613182024613182024613182024
8Worker 1XXXXXXXXX
9Worker 2XXXXXXXXXX
10Worker 3XXXXXXXX

</tbody>
Sheet1

Worksheet Formulas
CellFormula
B4=B3
B5=IF(OR(B6=0,B6=20),"N","D")

</tbody>

<tbody>
</tbody>

as you'll notice i've added 2 auxiliary ranges to solve the merged cells issue. Hide them or group them if you want the same visual as you've had before.
From this roster i couldn't determine if there were days off. so ...

Then for the reporting grid:
Excel 2016 (Windows) 64 bit
1december
2Worker 1
3Date01020304050607080910111213141516171819202122232425262728293031Total
4Worked hours0000000000000000000000000065116533
5Night hours000000000000000000000000006046016
6Day off hours

</tbody>
Sheet2

Worksheet Formulas
CellFormula
B4=SUMPRODUCT((Sheet1!\$B\$4:\$AJ\$4=B\$3)*(Sheet1!\$A\$8:\$A\$10=\$A2)*(Sheet1!\$B8:\$AJ10="X")*((Sheet1!\$B\$7:\$AJ\$7)-(Sheet1!\$B\$6:\$AJ\$6)))
B5=SUMPRODUCT((Sheet1!\$B\$4:\$AJ\$4=B\$3)*(Sheet1!\$A\$8:\$A\$10=\$A2)*(Sheet1!\$B\$5:\$AJ\$5="N")*(Sheet1!\$B8:\$AJ10="X")*((Sheet1!\$B\$7:\$AJ\$7)-(Sheet1!\$B\$6:\$AJ\$6)))

</tbody>

<tbody>
</tbody>

Hi ,
I've tried to update my excel file according to your proposal. It's very nice but I got some troubles : 1. I couldn't generalise B4=B3's formula to the other cells(it gave wrong results)
2. The =IF(OR(B6=0,B6=20),"N","D") formula doesn't give the correct information , it's only if B6=0 that is taken into account. I'm wondering if it isn't linked to the format of the cells(if yes should I change the format of the cells to number or text(by default I chose hour)).

The other formulas work well but I'd like the night hour to be calculated from 21 to 6 and not from 20 to 6.
Second, is it possible to generate the other weeks of the month automatically and to write above each week the start and the end date of the week (i mean to not display any date from other months even if the monday or more days are not from the given month) . for example the first week od december 2016 is : from 12/01 to 12/03/2016 but the weekly shift will display all the days of the week).

Thanks a lot. If you give me your mail I'll send you what I did so that you that you'll double check it for me.

#### stoure

##### New Member
Hi jorismoering,
I sent you my file and my mail.
I'll still working on it but I've got some worries about it. Like month's week management and date linked management. I mean to display all the weeks if the month with the dates linked so that when processing the work hours it'll take the entire month days into account.

Thx..

#### stoure

##### New Member
 Below is what I did for the shift table SHIFT TABLE Month: déc 2016 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 Week from 12/12 au 18/12 2016 lundi 12 mardi 13 mercredi 14 jeudi 15 vendredi 16 samedi 17 dimanche 18 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 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 X X X X X X X X X Week from 19/12 au 25/12 2016 lundi 19 mardi 20 mercredi 21 jeudi 22 vendredi 23 samedi 24 dimanche 25 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 Worker 2 X X X X X X X X X Worker 3 X 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 X X X X X X X X X Week from 26/12 au 01/01 2017 lundi 26 mardi 27 mercredi 28 jeudi 29 vendredi 30 samedi 31 dimanche 01 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>

#### stoure

##### New Member
Here is what I did for worked hours calculation .
 WORKED HOURS SUMMARY month: déc 2016 code 01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 TOTAL day off Worker 1 worked hours 0 11 6 5 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 night hours day off hours Extra hours Worker 2 worked hours 0 4 6 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 night hours day off hours Extra hours Worker 3 worked hours 0 4 6 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 night hours day off hours Extra hours extra hours is hours spent beyong 180 hours of work : computed only at total level night hours is from 21 to 06

<colgroup><col><col><col><col><col span="4"><col><col span="11"><col><col span="12"><col></colgroup><tbody>
</tbody>

#### jorismoerings

##### Well-known Member
Hi,

Take a look at this:

Excel 2016 (Windows) 64 bit
1
2
3Monthdec 2016
4
5Nmatr.01020304050607080910111213141516171819202122232425262728293031TOTAL
6days Off
7
8agent1
9worked hours05110605110605110605000000000000000000000000000005110605110605120
10night hours0004060004060004060000000000000000000000000000000004060004060050
11day off hours0000000000000000000000000000000000000000000000000500000000000005
12night off hours0000000000000000000000000000000000000000000000000000000000000000
13extra hours0000000000000000000000000000000000000000000000000000000000000000
14agent2
15worked hours06051106051106051106110605110605110605110605110606051106051106227
16night hours06000406000406000406040600040600040600040600040606000406000406112
17day off hours0000000000000000000000000000000000000000000000000600000000000006
18night off hours0000000000000000000000000000000000000000000000000600000000000006
19extra hours0000000000000000000000000000000000000000000000000305110605110647
recap
Cell Formulas
RangeFormula
D9=SUMPRODUCT(--(OFFSET(shift!\$B\$8,(WEEKNUM(D\$5,1)-WEEKNUM(\$AC\$3,1))*14,MATCH(recap!D\$5,OFFSET(shift!\$C\$5,(WEEKNUM(D\$5,1)-WEEKNUM(\$AC\$3,1))*14,0,1,35),0),7,5)="X")*--(shift!\$B\$8:\$B\$14=recap!\$A8)*((TOV!\$A\$3:\$E\$3)-(TOV!\$A\$2:\$E\$2)))
D10=SUMPRODUCT(--(OFFSET(shift!\$B\$8,(WEEKNUM(D\$5,1)-WEEKNUM(\$AC\$3,1))*14,MATCH(recap!D\$5,OFFSET(shift!\$C\$5,(WEEKNUM(D\$5,1)-WEEKNUM(\$AC\$3,1))*14,0,1,35),0),7,5)="X")*--(shift!\$B\$8:\$B\$14=recap!\$A8)*((TOV!\$A\$4:\$E\$4)-(TOV!\$A\$2:\$E\$2))*((HOUR(TOV!\$A\$4:\$E\$4)>20)+(HOUR(TOV!\$A\$4:\$E\$4)<13)))
D11=IF(ISERROR(MATCH(D\$5,Table3[Day Off],0)),0,D9)
D12=IF(ISERROR(MATCH(D\$5,Table3[Day Off],0)),0,D10)
D13=IF(SUM(\$C9:D9)*24>180,SUM(\$C9:D9)-(180/24)-SUM(\$C13:C13),0)

Replies
5
Views
173
Replies
1
Views
129
Replies
4
Views
393
Replies
1
Views
306
Replies
8
Views
196

1,191,134
Messages
5,984,864
Members
439,921
Latest member
Neocold

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