7 day workweek timesheet - excluding lunch

[Guest]

I thought I alreayd posted this but don't see it anywhere, so forgive me if
this is duplicated somewhere.
I am looking for a formula to calculate total hours worked, to exclude
lunch, based on the assumption of a 30 min lunch if working 6 hours or more


a b c d e f g h i j k l m n o
name total start end start end start end start end start end start end start end
Kathy 36:00 15:00 19:00 5:00 13:30 5:00 13:30 5:00 13:30 5:00 13:30
Bob 28:00 15:00 19:00 5:00 13:30 5:00 13:30 5:00 13:30
Thanks in advance for your help

 

[Guest]

Yeah S, I have a complicated payroll sheet that uses the same premise. Have
an IF function evaluate the difference between the end time and start time to
see if it is less than or equal to 6, if it is not then use the difference
alone. If it is then have the formula deduct the half hour. It might look
like this:

=sum(if(b3-b2<=6,(c2-b2)-00:30,c2-b2),if(e2-d2<=6,(e2-d2)-00:30,e2-d2),if.....)

The IF function will be repeated 7 times for each day of the week, beacause
each day of the week will have to be evaluated separately. The SUM function
will add up all the results of the seven IF functions. I couldn't make sense
of the way you pasted the example chart into the question so you may have to
adjust the cell numbers.

Good luck

fryguy

 

[Guest]

Thanks fryguy - I am having a hard time duplicating the formula you gave.
Here is what I have set up (sorry the pasting didn't work)

Column A - name
Column B - start time (mon)
Column C - end time (mon)
Column D - start time (tue)
Column E - end time (tue)
Column F - start time (wed)
Column G - end time (wed)
Column H - start time (thu)
Column I - end time (thu)
Column J - start time (fri)
Column K - end time (fri)
Column L - start time (sat)
Column M - end time (sat)
Column N - start time (sun)
Column O - end time (sun)
Column P - total hours worked

Thanks again for your assistance

 

[Guest]

hey sorry about the delay. try this:

=SUM(IF((C3-B3)>=TIME(6,0,0),C3-B3-TIME(0,30,0),C3-B3),IF((E3-D3)>=TIME(6,0,0),E3-D3-TIME(0,30,0),E3-D3),IF((G3-F3)>=TIME(6,0,0),G3-F3-TIME(0,30,0),G3-F3),IF((I3-H3)>=TIME(6,0,0),I3-H3-TIME(0,30,0),I3-H3),IF((K3-J3)>=TIME(6,0,0),K3-J3-TIME(0,30,0),K3-J3),IF((M3-L3)>=TIME(6,0,0),M3-L3-TIME(0,30,0),M3-L3),IF((O3-N3)>=TIME(6,0,0),O3-N3-TIME(0,30,0),O3-N3))

Make sure you format the totals column to "time -> 37:30:55" otherwise when
the total hits 25 hours total it will show 01:00.

good luck

fryguy.

 

[Guest]

hey sorry about the delay. try this:

=SUM(IF((C3-B3)>=TIME(6,0,0),C3-B3-TIME(0,30,0),C3-B3),IF((E3-D3)>=TIME(6,0,0),E3-D3-TIME(0,30,0),E3-D3),IF((G3-F3)>=TIME(6,0,0),G3-F3-TIME(0,30,0),G3-F3),IF((I3-H3)>=TIME(6,0,0),I3-H3-TIME(0,30,0),I3-H3),IF((K3-J3)>=TIME(6,0,0),K3-J3-TIME(0,30,0),K3-J3),IF((M3-L3)>=TIME(6,0,0),M3-L3-TIME(0,30,0),M3-L3),IF((O3-N3)>=TIME(6,0,0),O3-N3-TIME(0,30,0),O3-N3))

Make sure you format the totals column to "time -> 37:30:55" otherwise when
the total hits 25 hours total it will show 01:00.

good luck

fryguy.