Staff hours calculator minus relevant breaks

Hi everyone,

I am trying to make a staff wage calculator in excel but need to come up with a formula to minus the breaks. I am not an excel pro lol and i'm not sure if what i'm asking for is possible? Here goes...

I would like to calculate staff hours worked minus breaks, the breaks are:
over 5 hours a 15 minute break
over 6 hours a 30 minute break
over 7 hours a 1 hour break
over 8 hours a 1 hour 30 minutes break.

So if someone worked 12:00(A1) to 19:00(B1) then (C1) would show 06:00 (after minusing the 1 hour break)

All staff are on different shifts so would need to be in one formula.

Any help please?

Many thanks in advance

Asif

sorry, i thought no one had replied to my thread as i did not receive any email? only logged onto the forum today to see replies!

Thank you very much for the solutions but i also have another query if anybody could help please.


I am trying to make a staff wage calculator in excel but need to come up with a formula to minus the breaks


If worked over 4 hours and 30 minutes then a 30 minute break is removed from the total shift.


So if someone worked 12:00(A1) to 19:00(B1) then (C1) would show 06:30 (after minusing the 30 minute break)

All staff are on different shifts so would need to be in one formula.


Asif