Calculating penalty rate hours in a shift that has differing penalty rates

[Mike_Geraghty]

Dear Members,

I have been working on this spreadsheet for some time. I have been kindly helped as I have proceeded, but I still have a recurring issue I cannot solve.

I am a bus driver and I am attempting to check my complicated payslip as there are continual mistakes with my pay, especially with a penalty rate that is calculated based on start and end times.

I have attached a full description of the problem I am trying to solve, which can be summarised as such;

1. If I start work before 7:00am and finish after 7:00am I am paid on a pro-rata basis those hours worked. For example I f I start work at 5:30am and finish at 1:30pm I am paid a 15% penalty rate for 1.5 hours.
2. If I start work before 8:00pm and finish after 8:00pm I am paid for the full shift. For example if start work at 3:00pm and finish at 11:00pm I am paid a 15% penalty rate for 8 hours.

The problem I am trying to solve is to create a formula (if possible) to calculate the varying shift times.

As such please also find attached a spreadsheet with a range of start and end time scenarios that I hope provides the entire scheme of penalty hours that I am trying to achieve.

Thank you, Michael

[#2]

@Mike_Geraghty 

To calculate penalty rate hours in a shift that has differing penalty rates, you can use a combination of Excel functions such as IF, TIME, and MOD. Below is a step-by-step guide on how to set up a formula to calculate penalty hours based on your provided criteria:

Suppose you have the following setup in your Excel spreadsheet:

• Cell A1: Start Time (e.g., "5:30 AM")
• Cell B1: End Time (e.g., "1:30 PM")

In cell C1, you can enter the following formula to calculate the penalty hours based on your rules:

=IF(B1<A1,0,IF(A1<TIME(7,0,0),0,IF(B1<TIME(7,0,0),(B1-A1),TIME(7,0,0)-A1)))

This formula checks the start and end times and calculates the penalty hours based on your criteria:

1. If the end time is earlier than the start time, the penalty hours are zero.
2. If the start time is before 7:00 AM, the penalty hours are calculated as the difference between the end and start times.
3. If the start time is after 7:00 AM but before 8:00 PM, the penalty hours are calculated as the time until 7:00 AM (i.e., "7:00 AM" - Start Time).
4. If the start time is after 8:00 PM, the penalty hours are zero.

You can adjust this formula to your specific scenario by changing the cell references (A1 and B1) to the corresponding cells in your spreadsheet.

Now, apply this formula to the entire column where you have shift start and end times, and it will calculate the penalty hours based on your rules for each row.

Remember that this is just an example, and you can modify the formula as needed to suit your specific shift data and penalty rate rules. I not open your file, for personal security reasons. The text was created with the help of AI.

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.

[#3]

Hi NikolinoDE,

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

[#4]

@Mike_Geraghty 

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.