Calculating an hourly penalty rate within a shift that may have non-penalty hours within that shift.

[Mike_Geraghty]

Dear discussion members,

I have tried finding if there is an issue already addressed with my problem, and I have not been able to find any to date.

Therefore I need your kind assistance and draw upon far more expertise than I have.

I am putting together a spreadsheet to calculate my wages and I am trying to find a solution for the following problem:

1. I am paid a penalty rate of 15% for working the hours from 8:00pm to 7:00am the following morning.
2. My start and end times may vary, depending on the shift allocated to me.
For example some days I may start at 5.00pm and finish at 1:30am, other days I may start at 9:00pm and finish at 10:00am.
Generally there is a 50 minute break.
3. So as you can see some hours are not paid a penalty rate, that is if I am working before 8:00pm and conversely if I finish after 7:00am.

How do I calculate those hours not incurring the penalty rate and those hours that are incuriing the penalty rate.

Thank you for kind help .. Michael

[#2]

@Mike_Geraghty 

Could you attach a sample workbook without sensitive information, or make it available through OneDrive, Google Drive, Dropbox or similar?

[#3]

Hi Hans,
I have no problem sending you the file, thank you for your kind assistance.
I have a Google Drive account.
As a background for you I am a bus driver in Sydney, Australia. Recently our company was taken over, and since then there has been issues with incorrect pay.
I am familiar with excel at a rudimentary level, and decided to try to build my own wages/pay calculator in order to verify if my pay is correct, before lodging a query with the accounts department.
This exercise is definitely challenging my abilities, and thus the reason for my post for help, but I am thoroughly enjoying learning excel at a new advanced level.
Please provide me your e-mail details so I can send the workbook to you.
Cheers Michael

[#4]

@Hans Vogelaar 

Hi Hans,

Please find attached the workbook I have been working on.

I hope you have received my previous post re the reason for my inquiry.

Also note I am using an old laptop with an old version of excel.

I have a number of issues to resolve,

1. I am trying to calculate a penalty rate. A friend has tried to help but we are having problems as you can see.

For example if I am not working on that day then I still have a default of 4 hrs is calculated, and we cannot calculate the 2nd. half.

2. Another issue is I have to build a VLOOKUP so that when I enter in a Work Status (Column C and Drop Down List sheet)) information is calculated by the specific cell. For example if I have annual leave the default time of 8 hrs will show in column AA. Conversely if I am sick it will show in AB.

Anyway step-by-step. Right now I need to resolve the penalty rate issue.

Once again thank you ... Cheers Michael

[#5 · Accepted solution]

@Mike_Geraghty 

In the attached version, I have cleared the start and end times for day you don't work,

Instead of first calculating the penalty hours, I have first calculated the normal hours, then calculated penalty hours as total - normal.

[#6]

@hans Vogelar
Thank you sooooo much.
I can now push ahead with what I am capable of.
Is it possible to seek your assistance further if so required?
My next phase is putting together VLOOKUP table so when I enter in the work status drop down entry the start time will recognise it to be zero where applicable.
For example if I have Annual Leave as the work status then column AA will show the desired hours, in this case 8. Conversely for sick day, and so on.
I do hope I am not adversly drawing on your 'intellectual property' ... hahha.
As a side note from a excel professional, do you think the way I am setting it out looks easy to read and interpret?
Kind regards once again ... Michael

[#7]

@Mike_Geraghty 

You cannot use a formula for the start time, since you want to be able to enter the start time manually in most situations. It would be possible to use VBA code, but that might be overkill:

• It would disable undo when the user changes work status.
• It would require all users to enable macros when they open the workbook.

In the version I attached to my previous reply, I already entered formulas in columns AA and AB to fill in annual leave hours and sick day hours based on work status.

[#8]

@Hans Vogelaar 

Thank you Hans,

I was not expecting you to help me so much, that is why I did not notice what you kindly did to help me.

How do I arrange a case of beers to be sent to you ??? Or a single malt whisky ??

Cheers ... Michael

[#9]

@Mike_Geraghty 

Thanks for your kind offer, much appreciated, but that's not necessary.

[#10]

@Hans Vogelaar 

Hi Hans,

I sincerely need your help again, if possible. I do feel I am intruding on your time and knowledge and for that reason I am uncomfortable, but in this instance I look to you as my on line mentor

I have 2 issues I am struggling with, and I have attached the workbook file;

Issue #1.

I refer to the worksheet "Penalty Rate Calculator". It has occurred to me that I did not include the Voluntary Overtime worked into the penalty rate hours. I went ahead and gave it my best shot thinking I should be able to insert the same formulas from the 2nd. half but it is not working for me. 

Where am I going wrong? And on that basis, so I can learn more, is it possible to explain how you put the formulas together? If that is touching on your intellectual property I understand and withdraw that request.

Issue #2.

I refer to the worksheet "Work Calculator", specifically Broken Shift Penalty, (cells highlighted in yellow).

A broken shift is where I drive the bus for maybe 4 hours in the morning peak period. I then have maybe a 4 hour break, then re-commence my shift driving for maybe 4 hours in the evening peak time. Note these drive times and subsequent break may vary. 

I am then paid an increase in my hourly rate if I drive a bus for longer than 9.5 hours. (an explanation is entered in "Calculator Parameters" A45 - ITEM 7. BROKEN SHIFTS

Basically if I drive a bus for 9.5 to 10.5 hours I am paid at a rate of time and a half. Thereafter I am paid at double time.

I have created a number of different scenarios in Work Calculator.

Thanking you in advance,

Michael

[#11]

@Mike_Geraghty

Hi Mike.

About Issue #1: you did it correctly, but you made one tiny mistake: in the penalty formula for Sunday, you multiply with 0 to cancel the penalty. In column U, you then filled the formula down from Sunday, causing ALL penalties in that column to be canceled.

Here follows a lengthy explanation of the formula.

The formula in columns F, M and T calculate normal working hours. In F7:

=IF(C7="","",24*(MAX(MIN(D7+(D7<C7),TIME(20,0,0))-MAX(C7,TIME(7,0,0)),0)+IF(D7<C7,MAX(D7-TIME(7,0,0),0),0)))

The formula first checks if the start time in C7 has been filled in. If not, it returns a blank "".

Otherwise, it calculates the normal working time, and multiplies the result with 24 to convert the time to decimal hours. The actual calculation consists of two parts:

MAX(MIN(D7+(D7<C7),TIME(20,0,0))-MAX(C7,TIME(7,0,0)),0)

and

IF(D7<C7,MAX(D7-TIME(7,0,0),0),0)

In the first part:

MIN(D7+(D7<C7),TIME(20,0,0)) is the end time, but if if is later then 20:00 (8 PM), take 20:00.

MAX(C7,TIME(7,0,0)) is the start time, but if it is earlier than 7:00 (7 AM), take 7:00.

We subtract the start time from the end time, but if the result is negative (because working time falls outside normal hours), take 0.

The second part only applies if end time is before start time in clock time, i.e. start time is before midnight and end time is after midnight, for example start=23:00 and end=8:00. In that case, MAX(D7-TIME(7,0,0) calculates the time worked after 7:00 (7 AM). If this is negative because end time was before 7:00, take 0.

I'll tackle Issue #2 in my next reply.

[#12]

@Mike_Geraghty 

Issue #2: see the attached version.

[#13]

Hi Hans,
I have noticed that my previous reply a few days ago, in response to your help, may not have successfully posted.
Nonetheless once again thank you. I did show my wife how much you have helped us, and as she is Malaysian she wants to also offer you her appreciation in the only way she knows, that being sending you her signature chicken curry
I did tell her that I do not think you reside in Australia so it may not be possible, but I was touched by her understanding of the immense amount of knowledge you have, and how you helping me can ensure I am paid my correct wage.
I will soon be able to start a full audit of my payslips over the previous 10 months, but already I have picked up discrepancies.
My employer does, in no way, have any ill intent in this regard. I believe they do not have a salary/wage software that is aligned specifically to our complex industry award.
Once again thank you ... Michael

[#14]

@Mike_Geraghty Hi Mike,

That's very nice of your wife. I live in Europe, so it won't be possible, but please tell her that I really appreciate the idea!