Tracking work hours and calculating OT with multiple shifts/pay rates

**jlordson** · 08-14-2021, 12:10 AM

Still relatively new to advanced functions in excel. I work in a machine shop that runs 24/7. I'm trying to create a by-weekly time card calculator for multiple shifts/pay rates with OT after 40 hours/week. I'm also calculating hours using the 7 minute rule. I have things figured out for rounding time with the 7 minute rule, calculating hours per day and figuring pay rates with shift premiums and OT pay. I have a column of cells noted 1 or 2 depending on my shift for the day. What I can't seem to figure out is how to total the hours to get straight pay for each shift then OT for each shift after 40 total hours. I work 12 or more hours per shift both days and nights bouncing from one to the other during the week.

Example:
Sun 5pm-6am
Mon 5pm-6am
Tue 5:30 pm-7am
Wed 6pm-6am
Thur off
Fri 5:30am-6pm
Sat 6am-6pm

totals should be
40 nights
11.5 nights OT
0 days
24.5 day OT
for a total of 76 hours overall

I just can't figure out how to get there.
Any help would be awesome.

Thanks in advance

**jlordson** · 08-14-2021, 12:25 AM

I forgot to mention I have separate rates for holidays and vacation. I have them noted as 3 and 4 respectively in the cells in the shift identifier column. Where holidays count towards the 40 hours before OT but vacation does not.

**JeteMc** · 08-24-2021, 04:57 PM

Hello jlordson and Welcome to Excel Forum.
The following seems to work for the file provided, however I imagine that it will need refinement.
Five helper columns are added to each week.
The first helper column calculates the total hours using: =SUM(D$3:D3)
The second and fourth calculate the regular hours per shift using: =IF(E3=G$2,MIN(SUMIFS(D$3:D3,E$3:E3,E3),40),G2)
The third and fifth calculate the overtime hours per shift using: =IF(E3=H$2,F3-G3,0)
The formulas for the weekly totals are similar to: =IFERROR(MAX(IFS(G2=1,G3:G9,I2=1,I3:I9)),0)
Let us know if you have any questions.

**josephteh** · 08-24-2021, 10:54 PM

jlordson, is OT only applicable after a certain hours of work in a day/night?

Tracking work hours and calculating OT with multiple shifts/pay rates

LinkBack

Thread Tools

Rate This Thread

Display

Tracking work hours and calculating OT with multiple shifts/pay rates

Re: Tracking work hours and calculating OT with multiple shifts/pay rates

Re: Tracking work hours and calculating OT with multiple shifts/pay rates

Re: Tracking work hours and calculating OT with multiple shifts/pay rates

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Calculating multiple pay rates based on time of day worked, not number of hours worked

Calculating regular hours based on to 2 different pay Rates

[SOLVED] Calculating no of hours at day or nighttime for shifts

Calculate hours worked per day where we have multiple shifts per day and shifts span 12pm

[SOLVED] Calculating working shifts between two hours

Calculating the Number of Hours per Month, Allowing for Daylight-Saving Shifts

[SOLVED] A function that separates hours worked in work shifts that overlap

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions