Night Differential and two different overtime rate

[Deedee24]

hi
I need to figure out how to have excel calculate my hours and rate of pay when I work different shifts.
Example:
when I work my regular 80 hour week I get paid regular rate. Anything over 80 hours to 89 hours I get paid time and a half. Anything over 90 hours I get double time. Also I get paid night differential anytime I work from 7pm - 7am.
Can anyone help
Please

[JohnTopley]

Attach a sample workbook (not image).

Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate.

Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[samba_ravi]

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[Deedee24]

no sure if I did this correctly

[JeteMc]

This proposed solution employs two helper columns and two helper cells, all of which may be moved and/or hidden for aesthetic purposes.
The Total Hrs. column is populated using the formula: =E13-D13
The Cumulative Hrs. column is populated using the formula: =SUM(J13,K12)
The helper cells are H9:I9.
The formula that populates the Regular Hrs. column is: =IF(K13<=H$9,J13,IF(K12>=H$9,0,J13-(K13-H$9)))
The formula that populates the OT.1 column is: =IF(K13<=I$9,J13-F13,IF(K12>=I$9,0,J13-(K13-I$9)))
The formula that populates the OT.2 column is: =J13-F13-H13
The formula that populates the Night D column is: =IF(D13="",0,SUM(IF(D13< TIME(7,0,0),TIME(7,0,0)-D13,0),IF(E13>TIME(19,0,0),E13-TIME(19,0,0),0)))
Note: I am returning the file as an .xlsx as you profile indicates you are using the 2010 version.
Let us know if you have any questions.

[Deedee24]

Thanks everyone for your help.
JeteMc that's what I was looking for
Thank you

[AliGW]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

[JeteMc]

You're Welcome. Thank You for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.