Help on shift management

stoure · Dec 23, 2016

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

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

Thanks in advance!

jorismoerings · Dec 23, 2016

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 · Dec 23, 2016

"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

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 · Dec 23, 2016

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

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

T

U

V

W

X

Y

Z

AA

AB

AC

AD

AE

AF

AG

AH

AI

AJ

1

Week from 12/01 to 12/03 2016

2

3

27-12-2016

28-12-2016

29-12-2016

30-12-2016

31-12-2016

1-1-2017

2-1-2017

4

27

28

29

30

31

1

2

5

N

D

N

D

N

D

N

D

N

D

N

D

N

D

N

6

Shift begining

0

6

13

18

20

0

6

13

18

20

0

6

13

18

20

0

6

13

18

20

0

6

13

18

20

0

6

13

18

20

0

6

13

18

20

7

Shift end

6

13

18

20

24

6

13

18

20

24

6

13

18

20

24

6

13

18

20

24

6

13

18

20

24

6

13

18

20

24

6

13

18

20

24

8

Worker 1

X

9

Worker 2

X

10

Worker 3

X

Sheet1

Cell Formulas
Range	Formula
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

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

T

U

V

W

X

Y

Z

AA

AB

AC

AD

AE

AF

AG

1

december

2

Worker 1

3

Date

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

4

Worked hours

0

6

5

11

6

5

33

5

Night hours

0

6

0

4

6

0

16

6

Day off hours

Sheet2

Cell Formulas
Range	Formula
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)))

stoure · Dec 23, 2016

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 · Dec 23, 2016

jorismoerings said:
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

A B C D E F G H I J K L M N O P Q R S T U V W X Y Z AA AB AC AD AE AF AG AH AI AJ
1 Week from 12/01 to 12/03 2016
2
3 27-12-2016 28-12-2016 29-12-2016 30-12-2016 31-12-2016 1-1-2017 2-1-2017
4 27 27 27 27 27 28 28 28 28 28 29 29 29 29 29 30 30 30 30 30 31 31 31 31 31 1 1 1 1 1 2 2 2 2 2
5 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
6 Shift begining 0 6 13 18 20 0 6 13 18 20 0 6 13 18 20 0 6 13 18 20 0 6 13 18 20 0 6 13 18 20 0 6 13 18 20
7 Shift end 6 13 18 20 24 6 13 18 20 24 6 13 18 20 24 6 13 18 20 24 6 13 18 20 24 6 13 18 20 24 6 13 18 20 24
8 Worker 1 X X X X X X X X X
9 Worker 2 X X X X X X X X X X
10 Worker 3 X X X X X X X X

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet1

Worksheet Formulas

Cell Formula
B4 =B3
B5 =IF(OR(B6=0,B6=20),"N","D")

<thead>
</thead><tbody>
</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

A B C D E F G H I J K L M N O P Q R S T U V W X Y Z AA AB AC AD AE AF AG
1 december
2 Worker 1
3 Date 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
4 Worked hours 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 6 5 11 6 5 33
5 Night hours 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 6 0 4 6 0 16
6 Day off hours

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet2

Worksheet Formulas

Cell Formula
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)))

<thead>
</thead><tbody>
</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 · Dec 24, 2016

Hi jorismoering,
I sent you my file and my mail.
Hope you've received.
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 · Dec 24, 2016

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

stoure · Dec 24, 2016

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

jorismoerings · Dec 27, 2016

Hi,

Take a look at this:

Excel 2016 (Windows) 64 bit

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

T

U

V

W

X

Y

Z

AA

AB

AC

AD

AE

AF

AG

AH

1

2

3

Month

dec 2016

4

5

Nmatr.

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

6

days Off

7

8

agent1

9

worked hours

05

11

06

05

11

06

05

11

06

05

00

05

11

06

05

11

06

05

120

10

night hours

00

04

06

00

04

06

00

04

06

00

04

06

00

04

06

00

50

11

day off hours

00

05

00

05

12

night off hours

00

13

extra hours

00

14

agent2

15

worked hours

06

05

11

06

05

11

06

05

11

06

11

06

05

11

06

05

11

06

05

11

06

05

11

06

05

11

06

05

11

06

227

16

night hours

06

00

04

06

00

04

06

00

04

06

04

06

00

04

06

00

04

06

00

04

06

00

04

06

00

04

06

00

04

06

112

17

day off hours

00

06

00

06

18

night off hours

00

06

00

06

19

extra hours

00

03

05

11

06

05

11

06

47

recap

Cell Formulas
Range	Formula
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)

Help on shift management

stoure

New Member

Excel Facts

jorismoerings

Well-known Member

stoure

New Member

jorismoerings

Well-known Member

stoure

New Member

stoure

New Member

stoure

New Member

stoure

New Member

stoure

New Member

jorismoerings

Well-known Member

Similar threads

Forum statistics

Share this page

Help on shift management

New Member

Excel Facts

Well-known Member

New Member

Well-known Member

New Member

New Member

New Member

New Member

New Member

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock