Hello, I have a problem calculating a night shift premium for night workers. Shifts that fall within 10pm to 6am next have a 10% addition to their salary. So if the shift start at 9pm and ends at 5am, you will have 8 hours of night shift premium, 11pm would have 7. How do I make a formula that would do this? My formula right now works only till about 11pm. Once it hits 12am, it goes negative.
Hi
Was there a typo there? If the premium doesn't apply to times before 10pm then a 9pm-5am would only be 7 premium hours and a 11pm-5am would be 6.
I've assumed it was a typo and that 9pm is the start of premium time so assuming Start and end times are in A1 & B1 as time numbers, i.e. decimals of a day then one way to give the allowable hours is
You'll obviously need to wrap it in another If test to check for a start after 9pm and then increase the hours by 10% if so.
Please Login or Register to view this content.
Regards
Richard Buttrey
RIP - d. 06/10/2022
If any of the responses have helped then please consider rating them by clicking the small star iconbelow the post.
Perhaps a formula like D3=MOD(C3-B3,1) would be of help?
You could also format target cell D3 as "h:mm"
Alf
Yes your right there was a typo. 10pm-6am are the hours where the employee will be paid a premium. So if your shift is from 10pm-7am with 1 hour lunch break, then night premium should be 8 hours. If shift is from 9pm to 6am, then the premium start counting from 10pm-11pm for a total of 7 hours, 11pm-8am, 7 hours, etc.. How do I do this so it will only start counting after 10pm.
A1 6/8/2011 9:00:00 PM
B1 6/9/2011 6:00:00 AM
Thanks
Last edited by sexybeast; 06-08-2011 at 08:10 AM. Reason: add info and delete quote
sorry, you can delete the quote if you want.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks