Automatically calculate Regular Time, OT, and Dbl Time based on several Criteria

[egrospe17]

Hello Excel Geniuses,

I'm having a hard time coming up with solutions here using formulas. Here's the synopsis:

I work at a company where an employee usually works 1 job a day or multiple jobs a day.

1. Scenario 1: Employee 1 works 1 13 hour shift
   • Hours Calc: Regular (8hrs) OT (4hrs) Dbl (1hr)
2. Scenario 2: Employee 2 works two jobs in the same day. Job 1 = 8 hour shift Job 2 = 8 hour shift. Total hours for the day = 16 hrs
   • Hours Calc:
     • Job 1: Regular (8hrs)
     • Job 2: Regular (0hrs) OT (4hrs) Dbl (4hrs)

So we follow the standard rule here. On a given day, Anything more than 8 hours is OT and anything more than 12 hours is dbl time. Also, we need to follow the rule that if the sum of regular time equals 40 hours a week, then the remaining hours for the rest of the week must be distributed between OT and Dbl time.

Below is a sample data export from our time recording system. The column "2nd job Indicator" indicates that the specific job (row) is the job is the 2nd job of the day.

So my question is, given the conditions above, what can I do to calculate regular, OT, and dbl time accordingly? Any help is appreciated.

Date	Emp ID	Employee Name	Job No.	Project No.	Total Hours	2nd job Indicator	Weekending
5/4/2020​	1097​	EMPLOYEE 1	10418​	860823​	12.5​	0​	5/9/2020​
5/5/2020​	1097​	EMPLOYEE 1	10418​	860823​	12.5​	0​	5/9/2020​
5/6/2020​	1097​	EMPLOYEE 1	10418​	860823​	12.5​	0​	5/9/2020​
5/7/2020​	1097​	EMPLOYEE 1	10418​	860823​	12.5​	0​	5/9/2020​
5/8/2020​	1097​	EMPLOYEE 1	10418​	860823​	8​	0​	5/9/2020​
5/8/2020​	1097​	EMPLOYEE 1	27750​	881981​	8​	1​	5/9/2020​
5/9/2020​	1097​	EMPLOYEE 1	10418​	860823​	12​	0​	5/9/2020​
5/4/2020​	1101​	EMPLOYEE 2	27005​	820021​	8​	0​	5/9/2020​
5/5/2020​	1101​	EMPLOYEE 2	26575​	518110​	13.5​	1​	5/9/2020​
5/6/2020​	1101​	EMPLOYEE 2	27646​	593310​	8​	0​	5/9/2020​
5/7/2020​	1101​	EMPLOYEE 2	26575​	518110​	12​	0​	5/9/2020​
5/8/2020​	1101​	EMPLOYEE 2	26575​	518110​	8​	0​	5/9/2020​
5/8/2020​	1101​	EMPLOYEE 2	26575​	102339​	12​	1​	5/9/2020​

 

[Dave Patton]

I will show an extract of 2 alternatives.
You can adapt to your layout and modify if necessary for the rules in your jurisdiction.
You can copy the post to your spreadsheet and review the formulas.

T201901a.xlsm
	C	D	E	F	G	H	I	J	K
9	Version 2	Day1	Day2	Day3	Day4	Day5	Day6	Day7	Total
10	Hours	5	5	7	13	13	8	10	61
11	OT @ 2.0								4
12	Regular								40
13	OT @ 1.5								17
14
1aa

Cell Formulas
Range	Formula
K10	K10	=SUM(D10:J10)
K11	K11	=SUMPRODUCT(--(D10:I10>12),D10:I10-12)+(J10>8)*(J10-8)
K12	K12	=MIN(40,SUM(D10:I10)-SUMPRODUCT(--(D10:I10>8),D10:I10-8))
K13	K13	=K10-K11-K12

 

[Dave Patton]

T201901a.xlsm
	C	D	E	F	G	H	I	J	K	L	M
2		Jan 07 19	Jan 08 19	Jan 09 19	Jan 10 19	Jan 11 19	Jan 12 19	Jan 13 19
3	Version 1	Day 1	Day 2	Day 3	Day 4	Day 5	Day 6	Day 7	Total	Adj	Total
4	Hours	5	5	7	13	13	8	10	61		61
5	Regular	5	5	7	8	8	8	0	41	-1	40
6	OT @ 2.0	0	0	0	1	1	0	2	4		4
7	OT @ 1.5	0	0	0	4	4	0	8	16	1	17
1aa

Cell Formulas
Range	Formula
K4:K6	K4	=SUM(D4:J4)
D5:J5	D5	=MIN(8,D4)*(WEEKDAY(D2,2)<7)
L5	L5	=-L7
D6:J6	D6	=(D4>12)*(D4-12)+(COUNT($D$2:D2)=7)*(D4>8)*(MIN(4,D4-8))
M4:M7	M4	=K4+L4
D7:K7	D7	=D4-D5-D6
L7	L7	=(K5>40)*(K5-40)

 

[Dave Patton]

Check this alternative

T201901a.xlsm
	C	D	E	F	G	H	I	J	K
9	Version 2	Day1	Day2	Day3	Day4	Day5	Day6	Day7	Total
10	Hours	5	5	7	13	13	8	10	61
11	OT @ 2.0								2
12	Regular								40
13	OT @ 1.5								19
1aaa

Cell Formulas
Range	Formula
K10	K10	=SUM(D10:J10)
K11	K11	=SUMPRODUCT(--(D10:J10>12),D10:J10-12)
K12	K12	=MIN(40,K10-SUMPRODUCT(--(D10:J10>8),D10:J10-8))
K13	K13	=K10-K11-K12

 

[egrospe17]

Check this alternative

T201901a.xlsm
	C	D	E	F	G	H	I	J	K
9	Version 2	Day1	Day2	Day3	Day4	Day5	Day6	Day7	Total
10	Hours	5	5	7	13	13	8	10	61
11	OT @ 2.0								2
12	Regular								40
13	OT @ 1.5								19
1aaa

Cell Formulas
Range	Formula
K10	K10	=SUM(D10:J10)
K11	K11	=SUMPRODUCT(--(D10:J10>12),D10:J10-12)
K12	K12	=MIN(40,K10-SUMPRODUCT(--(D10:J10>8),D10:J10-8))
K13	K13	=K10-K11-K12

Hi Dave,
Thank you so much for your help. The challenge I have is the way i need the data arranged. With your idea i will have to have the dates arranged horizontally. My data needs to be arranged as shown on the picture below. So what I'm struggling with is how can i tell excel to distribute the hours based on the rules mentioned above and explained on the table below?

Some rules for the hours are:
On a given week (in this case 5/3/2020 to 5/9/2020)
Anything over 40 hours regular will have to be distribution to OT
Anything over 40 hours OT will have to be distributed to Double Time

.

Thanks again for your help... I've been struggling with this for weeks now.

 

[Dave Patton]

The following is consistent with your initial information.
The definition of OT after 40 hours is not clear.
You can edit the formula for your exact requirements.

T201901a.xlsm
	B	C				G	H	I	J
1	Date					Time	OT 2	Reg	OT 1.5
2	03-May-2020					12.5
3	04-May-2020					12.5
4	05-May-2020					12.5
5	06-May-2020					12.5
6	07-May-2020					16.0
7	08-May-2020					8.0
8	09-May-2020					12.0
9		Totals				86.0	6	40	40.0
1aaa_

Cell Formulas
Range	Formula
B3:B8	B3	=B2+1
G9	G9	=SUM(G2:G8)
H9	H9	=SUMPRODUCT(--(G2:G8>12),G2:G8-12)
I9	I9	=MIN(40,G9-SUMPRODUCT(--(G2:G8>8),G2:G8-8))
J9	J9	=G9-H9-I9

 

[Dave Patton]

A couple of ideas that you can experiment with.

T201901a.xlsm
	B	C				G	H	I	J
1	Date					Time	OT 2	OT 1.5	Reg
2	03-May-2020					12.5	0.5	4	8.0
3	04-May-2020					12.5	0.5	4	8.0
4	05-May-2020					12.5	0.5	4	8.0
5	06-May-2020					12.5	0.5	4	8.0
6	07-May-2020					16.0	4	4	8.0
7	08-May-2020					8.0	0	8	0.0
8	09-May-2020					12.0	4	8	0.0
9		Totals				86.0	10.0	36.0	40.0
10
11						86.0	10.0	36.0	40.0
1aaa_

Cell Formulas
Range	Formula
H2:H7	H2	=(G2>12)*(G2-12)
I2:I6	I2	=(G2>8)*MIN(4,G2-8)
J2:J8	J2	=G2-H2-I2
I7:I8	I7	=IF(SUM(J$2:J6)=40,G7-H7)
H8	H8	=(G8>8)*(G8-8)
B3:B8	B3	=B2+1
G9:J9	G9	=SUM(G2:G8)
G11	G11	=G9
H11	H11	=SUMPRODUCT(--(G2:G7>12),G2:G7-12)+(G8>8)*(G8-8)
I11	I11	=G11-H11-J11
J11	J11	=MIN(40,G9-SUMPRODUCT(--(G2:G8>8),G2:G8-8))