Please see attached an excel sheet with formulas done as below though I am struggling to get this completely correct when the start and end times are entered in half past times
Calculations/formulas as below
If worked from 6 hours to 11 hours per day = Deduct 30 minutes break time
If worked from 6 hours to 11 hours per day = Deduct 60 minutes break time
Employee 1 all fine with the total hours on shift, relevant break time deducted etc.
However, when the rota time is entered as half past the total hours becomes incorrect. Please see reference Employee 2. Rota Hrs (AG10) shows 8.3 instead of 8.5 and Pay Hrs (AH10) shows 7.3 instead of 7.5
Employee 3- Rota Hrs(AG13) shows 15.6 instead of 16 and Pay Hrs (AH13) shows 14.6 instead of 15 as I expected.
Could you please help me with this?
Notes
As the rotas are done by others I have formatted the IN and OUT time rows are formatted as numbers to avoid semicolon. However, inserting .5 for half past would confuse the staff so inserting as .3 (Example cell P10- inserted as 14:30 instead of 14.50)
Texts H,BH and LD are calculated as 7.5 hours and the total appears in Extra Hrs Column (AI) and then adds to total pay Hrs (AJ)
Texts E and L are calculated as 8 hour shift and calculates the same with deducting 0.5 hours
Bookmarks