# Employee roster with 30 0r 60 minutes break deduction based on hours worked

1. ## Employee roster with 30 0r 60 minutes break deduction based on hours worked

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.

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)
 Texts  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

2. ## Re: Employee roster with 30 0r 60 minutes break deduction based on hours worked

Hi
the trouble you are having is you have put in 14.30, this may be 2:30pm to us but Excel sees it as 14.3. If you put 14.5 your formula works.
thanks
Leigh

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1