Distribute total working hours to three shifts

[nmiskulin]

Hi all!

I want to distribute (divide) total working hours of an employee to shifts. There are three shifts: first - from 6 AM to 2 PM, second -from 2 PM to 10 PM, and third from 10 PM to 6 AM

simple example:

- on may 7th, The employee worked from 7 AM to 7 PM, it has to be devided to 7 hours of first shift, and 5 hours of second shift.

not so simple example

- employee started work on may 7th, 7 PM and finished on may 8th at 7 AM. His working hours are: 3 hours of second shift on the may 7th, 2 hours of third shift on may 7th, 6 hours on third shift may8 th and 1 hour of first shift on may 8th.

Also, I will have to deal with the weekends and holidays, which are paid different.

How can I accomplish this? I have tried with median function, but as I understands it can not deal with everything.

Thanks a lot

Neven

 

[thisoldman]

The first step is to calculate the start and end times for each date.

If a start time exists in that row but no end time, the end time is taken as midnight. If an end time exists but no start time, the start time is taken as midnight. The formulas also require midnight in the start column to be encoded as zero, midnight in the end column to be encoded as 24.

After start and end times, are calculated the working hours are split into their proper shifts with the formulas in columns AJ:AL.

Book1
	C	D	E	F	AH	AI	AJ	AK	AL
6	Date	Work start	Work end		Start	End	1st	2nd	3rd
7	1	14	23		14	23	0	8	1
8	2	7	19		7	19	7	5	0
9	3	19			19	24	0	3	2
10	4		7		0	7	1	0	6
11	5	10	15		10	15	4	1	0
12	6						0	0	0
Sheet1

Cell Formulas
Range	Formula
AH7	=IF(($D7="")*($E7=""),"",IF($D7="",0,$D7))
AI7	=IF(($D7="")*($E7=""),"",IF($E7="",24,$E7))
AJ7	=IF(($AI7="")+($AH7>=14),0, IF(($AH7<=6)*($AI7>6),MIN($AI7,14)-6, IF(($AH7>=6)*($AH7< 14),MIN($AI7,14)-$AH7,0)))
AK7	=IF(($AI7="")+($AH7>=22)+($AI7<14), 0, IF(($AH7<=14)*($AI7>14),MIN($AI7,22)-14, IF(($AH7>=14)*($AH7<22),MIN($AI7,22)-$AH7,0)))
AL7	=IFERROR($AI7-$AH7-SUM($AJ7:$AK7),0)

 

[thisoldman]

For the hours to appear in the report in their proper columns, I added a cell, controlled with a data validation dropdown, for the user to enter the month. Another cell is reserved for the user to enter the year. A separate block of cells was reserved for the user to enter Holiday dates manually. Two helper columns were added to the spreadsheet to determine the day of the week and if the day is a holiday.

Book1
	C	AB	AC	AD	AE	AF
5	Date				Helper Formulas
6	1				Weekday	IsHoliday
7	1		Enter Month		1	1
8	2		April		2	1
9	3				3	0
10	4		Enter Year		4	0
11	5		2018		5	0
12	6				6	0
13	7		Holidays		7	1
14	8		Mon 2018-01-01		1	0
15	9		Sun 2018-04-01		2	0
Sheet1

Cell Formulas
Range	Formula
C7	=DATE($AC$11, MATCH($AC$8,$AN$7:$AN$18,0), 1)
C8	=IF(MONTH(C7+1)=MONTH($C$7), C7+1, "")
AE7	=IFERROR(WEEKDAY($C7),0)
AF7	=--(ISNUMBER(MATCH($C7,$AC$14:$AC$28,0)))

 

[thisoldman]

Formulas are used to place the shift hours in their proper columns.

The formulas for the columns not shown are similar.

Book1
	C	D	E	F	G	H	I	J	K	L	M	N
4				Regular			Saturday			Sunday
5	Date	Work start	Work end	1st shift	2nd shift	3rd shift	1st shift	2nd shift	3rd shift	1st shift	2nd shift	3rd shift
6	1	2	3	4	5	6	7	8	9	10	11	12
7	1	14	22		8
8	2	7	19				7	5
Sheet1

Cell Formulas
Range	Formula
F7	=($AE7>1)*($AE7< 7)*($AF7<>1)*$AJ7
G7	=($AE7>1)*($AE7< 7)*($AF7<>1)*$AK7
H7	=($AE7>1)*($AE7< 7)*($AF7<>1)*$AL7
I7	=($AE7=7)*($AF7=0)*$AJ7
J7	=($AE7=7)*($AF7=0)*$AK7
K7	=($AE7=7)*($AF7=0)*$AL7
L7	=($AE7=1)*($AF7=0)*$AJ7
M7	=($AE7=1)*($AF7=0)*$AK7
N7	=($AE7=1)*($AF7=0)*$AL7

 

[thisoldman]

I used conditional formatting to highlight weekends and holidays. Custom number formatting was used to hide zeros in the shift table.

A copy can be downloaded from: https://www.dropbox.com/s/i37vzgf7jvmbb29/nmiskulin_monthly_timesheet.xlsx?dl=0