Calculating Hours worked and subtracting lunch breaks ( break depending on hours worked )

Ive created a table to show a rota for work.

the table was easy enough to produce but what i really need is for excel to deduct unpaid breaks that employees take but the length of the lunch depends on how many hours worked that day
every one starts at different times and different days example

Mon Tue Wed etc
Start | Finish | Hrs Srt | Fin| Hrs Srt| Fin | Hrs
Person A 0745 | 15:00 13:00 |22:00| OFF

Person B 15-00 | 22:00 OFF 0745 - 1700

and so the break rules are as followed :

if shift is Less than 6 hours = NO BREAK
between 6 hours - 7hrs 45min = 30 min BREAK
8 hours plus = 1 Hour break
Example
Person A on Monday would have 30 min break as shift is 7hrs and 15 min long but is paid for 6 hrs and 45 mins. and i would like the table to show the hours per day with the break already subtracted like " 6.45"

Tuesday = 1 hour break because shift is 9 hours long but paid for 8

another thing is my company likes to display hours as a decimal so i'm aware i have to multiply it by 24 on excel to give me the answer ie: 7hrs 45 mins is shown as 7.75 but i need to deduct the break first in order display it first

is there any formulas or a function i could do to show this ?

any advice is appreciated thank you

so i would like each hour coloumn to calculate the hours work adn then deduct the break acording to the rules

Example

on Monday Rachel is entitled to 30 mins break according to the rules as her shift is 7.25 hours long (7hrs 15mins)
so E6 should show 6.75 hours (6hrs 45min)

however on Tuesday her shift is 10.17 hours long resulting to an hour break so H6 should show 9.17 hours worked ( 9hours 10 mins)
and Wednesday no break

is it possible to find a formula that calculates the break and then also deduct it leaving only the worked hours she is to be paid ?

thank you again

Untitled.jpg

how do i attach an actual document it is only allow me attaching a image or URL

im so sorry about this im not used to using these forums

thank you for your help in advance

To attach an Excel file to your post,

• click Go Advanced,
• scroll down until you see Manage Attachments,
• click that and select Browse,
• select your file and click Open,
• click Upload and you will see your attachment below Upload Files from a website
• click Close this window,
• click Submit reply

After that you should see attachment in your post

lets see if it works now haha

yay is worked thank you so much