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

egrospe17

New Member
Joined
May 31, 2020
Messages
5
Office Version
  1. 365
Platform
  1. Windows
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.

DateEmp IDEmployee NameJob No.Project No.Total Hours2nd job IndicatorWeekending
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​
 

Some videos you may like

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

Dave Patton

Well-known Member
Joined
Feb 15, 2002
Messages
4,574
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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
CDEFGHIJK
9Version 2Day1Day2Day3Day4Day5Day6Day7Total
10Hours557131381061
11OT @ 2.04
12Regular40
13OT @ 1.517
14
1aa
Cell Formulas
RangeFormula
K10K10=SUM(D10:J10)
K11K11=SUMPRODUCT(--(D10:I10>12),D10:I10-12)+(J10>8)*(J10-8)
K12K12=MIN(40,SUM(D10:I10)-SUMPRODUCT(--(D10:I10>8),D10:I10-8))
K13K13=K10-K11-K12
 

Dave Patton

Well-known Member
Joined
Feb 15, 2002
Messages
4,574
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
T201901a.xlsm
CDEFGHIJKLM
2Jan 07 19Jan 08 19Jan 09 19Jan 10 19Jan 11 19Jan 12 19Jan 13 19
3Version 1Day 1Day 2Day 3Day 4Day 5Day 6Day 7TotalAdjTotal
4Hours55713138106161
5Regular557888041-140
6OT @ 2.0000110244
7OT @ 1.5000440816117
1aa
Cell Formulas
RangeFormula
K4:K6K4=SUM(D4:J4)
D5:J5D5=MIN(8,D4)*(WEEKDAY(D2,2)<7)
L5L5=-L7
D6:J6D6=(D4>12)*(D4-12)+(COUNT($D$2:D2)=7)*(D4>8)*(MIN(4,D4-8))
M4:M7M4=K4+L4
D7:K7D7=D4-D5-D6
L7L7=(K5>40)*(K5-40)
 

Dave Patton

Well-known Member
Joined
Feb 15, 2002
Messages
4,574
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Check this alternative
T201901a.xlsm
CDEFGHIJK
9Version 2Day1Day2Day3Day4Day5Day6Day7Total
10Hours557131381061
11OT @ 2.02
12Regular40
13OT @ 1.519
1aaa
Cell Formulas
RangeFormula
K10K10=SUM(D10:J10)
K11K11=SUMPRODUCT(--(D10:J10>12),D10:J10-12)
K12K12=MIN(40,K10-SUMPRODUCT(--(D10:J10>8),D10:J10-8))
K13K13=K10-K11-K12
 
Last edited:

egrospe17

New Member
Joined
May 31, 2020
Messages
5
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Check this alternative
T201901a.xlsm
CDEFGHIJK
9Version 2Day1Day2Day3Day4Day5Day6Day7Total
10Hours557131381061
11OT @ 2.02
12Regular40
13OT @ 1.519
1aaa
Cell Formulas
RangeFormula
K10K10=SUM(D10:J10)
K11K11=SUMPRODUCT(--(D10:J10>12),D10:J10-12)
K12K12=MIN(40,K10-SUMPRODUCT(--(D10:J10>8),D10:J10-8))
K13K13=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

hours dist - screenshot.png
.

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

Dave Patton

Well-known Member
Joined
Feb 15, 2002
Messages
4,574
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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
BCGHIJ
1DateTimeOT 2RegOT 1.5
203-May-202012.5
304-May-202012.5
405-May-202012.5
506-May-202012.5
607-May-202016.0
708-May-20208.0
809-May-202012.0
9Totals86.064040.0
1aaa_
Cell Formulas
RangeFormula
B3:B8B3=B2+1
G9G9=SUM(G2:G8)
H9H9=SUMPRODUCT(--(G2:G8>12),G2:G8-12)
I9I9=MIN(40,G9-SUMPRODUCT(--(G2:G8>8),G2:G8-8))
J9J9=G9-H9-I9
 

Dave Patton

Well-known Member
Joined
Feb 15, 2002
Messages
4,574
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
A couple of ideas that you can experiment with.

T201901a.xlsm
BCGHIJ
1DateTimeOT 2OT 1.5Reg
203-May-202012.50.548.0
304-May-202012.50.548.0
405-May-202012.50.548.0
506-May-202012.50.548.0
607-May-202016.0448.0
708-May-20208.0080.0
809-May-202012.0480.0
9Totals86.010.036.040.0
10
1186.010.036.040.0
1aaa_
Cell Formulas
RangeFormula
H2:H7H2=(G2>12)*(G2-12)
I2:I6I2=(G2>8)*MIN(4,G2-8)
J2:J8J2=G2-H2-I2
I7:I8I7=IF(SUM(J$2:J6)=40,G7-H7)
H8H8=(G8>8)*(G8-8)
B3:B8B3=B2+1
G9:J9G9=SUM(G2:G8)
G11G11=G9
H11H11=SUMPRODUCT(--(G2:G7>12),G2:G7-12)+(G8>8)*(G8-8)
I11I11=G11-H11-J11
J11J11=MIN(40,G9-SUMPRODUCT(--(G2:G8>8),G2:G8-8))
 

Watch MrExcel Video

Forum statistics

Threads
1,123,281
Messages
5,600,720
Members
414,401
Latest member
grenona2020

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top