Forum Discussion
Solved
Calculating an hourly penalty rate within a shift that may have non-penalty hours within that shift.
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
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.
Could you attach a sample workbook without sensitive information, or make it available through OneDrive, Google Drive, Dropbox or similar?
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
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.
- 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
