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.
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.
- 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 ... MichaelYou 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.
