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 up...
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.
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
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
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.
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
Thanks for your kind offer, much appreciated, but that's not necessary.
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
