Total hours minus 30 minutes

[dandel90]

Pleeaasee help. I am new to excel. I am trying to make a work schedule (roster) that has there weekly shift times wed-tues. I'm wanting my total column to equal hours worked for the week minus 30 unites if worked over 5hours for a shift. I have it calculating the hours for the week hoping there is a way to do it with the half taken off for each day over 5. If that makes sense.

 

[Kaper]

So everyone who is working more than 5:00 will have 30 minutes less counted?
Am I right? like: 6:15->5:45 ; 8:00 -> 7:30 and ... 5:15->4:45?
if it is the case, then use such approach:

=if(your_formula>TIMEVALUE("05:00:00"),your_formula-TIMEVALUE("00:30:00"),your_formula)

or shorter:

=if(your_formula>5/24,your_formula-1/48,your_formula)

By your formula i mean a formula used to calculate time worked each day (You probably calculate it as a difference of Time_out and Time_in each day.

Or you can do this in final column, like you have in I2:

=SUM(B2:H2)

a sum of times for a whole week, then modified formula will be:

=SUM(if(B2:H2>5/24,B2:H2-1/48,B2:H2))