Rota - working out unpaid breaks

HI All

Im pretty rubbish at excel...

Im trying to generate a formula that automatically works out on a rota, a staff members total hours worked, with unpaid breaks deducted over the course of a week

Shift patterns can range from 8am until 3am, with various start times. i know excel has trouble going past 23:59, so im not sure if this is possible, but anytime after 24:00 can be represented by the word 'Close' or 'CL'.

I will need two formulas, as there are currently two different break structures in use at my place of work for staff on different contracts.


1st formula rules of unpaid breaks
6-7.59 hrs worked = 30 min break
8hrs + = 60 min break

2nd formula rules of unpaid breaks
5.59hrs worked = 15 min break
6-7.59 worked = 45 min worth of breaks
8hrs + worked = 90 min worth breaks

Any Help would be greatly appreciated!

I have attached a very basic sample to help you visualise the layout etc.

Hi
See workbook attahced where I have put in both formulae you asked for.
Hope this helps.
Good luck.
Tony