Forum Discussion
Calculating penalty rate hours in a shift that has differing penalty rates
Unfortunately that formula does not work as I get a zero with every entry. Please feel free to access my spreadsheet. I have posted it for that reason.
Nonetheless I already have a formula embedded in the cells in Columns F and M, that correctly calculates the pro-rata penalty applicable to time before 7:00am.
My problem is finding a formula that also captures the total penalty time when the shift starts before 8:00pm and finishes after 8:00pm. That is if I start at 3:00pm and finish at 11:00pm the penalty should be 8 hours
I think I need an AND/OR condition, but I am struggling with that.
Thank you ... Michael
To calculate the penalty rate based on your scenarios, you can use Excel formulas. I'll provide you with a step-by-step guide on how to set up your Excel worksheet to handle these calculations. The key is to use IF statements to check the conditions for each scenario and then apply the appropriate penalty rate. Below, I'll outline the steps for each scenario.
Scenario 1 - Starting before 7:00 am and finishing after 7:00 am:
Assuming "Start Time" is in cell A2 and "End Time" is in cell B2, you can calculate the penalty rate in cell C2 using this formula:
=IF(AND(A2 < TIME(7, 0, 0), B2 > TIME(7, 0, 0)), (B2 - TIME(7, 0, 0)) + (TIME(7, 0, 0) - A2), 0)
Scenario 2 - Starting before 5:00 pm and finishing after 8:00 pm:
Assuming "Start Time" is in cell A3 and "End Time" is in cell B3, you can calculate the penalty rate in cell C3 using this formula:
=IF(AND(A3 < TIME(17, 0, 0), B3 > TIME(20, 0, 0)), B3 - A3, 0)
Scenario 3 - Starting after 5:00 pm and finishing before 7:00 am:
Assuming "Start Time" is in cell A4 and "End Time" is in cell B4, you can calculate the penalty rate in cell C4 using this formula:
=IF(AND(A4 > TIME(17, 0, 0), B4 < TIME(7, 0, 0)), B4 - A4, 0)
To handle all scenarios in a single cell using nested formulas, you can use nested IF statements.
Assuming you have "Start Time" in cell A2 and "End Time" in cell B2, you can calculate the penalty rate in cell C2 using the following nested formula:
=IF(AND(A2 < TIME(7, 0, 0), B2 > TIME(7, 0, 0)), (B2 - TIME(7, 0, 0)) + IF(AND(B2 > TIME(17, 0, 0), A2 < TIME(17, 0, 0)), TIME(17, 0, 0) - A2, 0), IF(AND(A2 < TIME(17, 0, 0), B2 > TIME(17, 0, 0)), B2 - TIME(17, 0, 0), 0))
You can copy these formulas down to calculate the penalty rates for multiple rows. The formulas check the conditions for each scenario and calculate the penalty rate accordingly.
Please adjust the cell references and times as needed for your specific worksheet. You can format the cells to display time or decimals to represent minutes, depending on your preference.
These formulas should help you calculate the penalty rates for different shift scenarios in your Excel sheet. The text, steps and formulas was created with the help of AI.
With your permission, I recommend always to inform about your Excel version, operating system, storage medium/hard drive, OneDrive, Sharepoint, etc.). If possible, add a file (without sensitive data) and use this file to describe your project step by step, or add photos with the appropriate description. In this link you will find some more information about it: Welcome to your Excel discussion space!
My answers are voluntary and without guarantee!
Hope this will help you.
Was the answer useful? Mark as best response and like it!
This will help all forum participants.