Thread: Time sheet

Hi All
Worked out how to total times worked for the week but need to deduct lunch breaks 30 minutes each day but ignore blanks (not to deduct the 30 minutes when blank) & ignore cells with text in
Please see sheet attached
text in drop down menus
When text entered total cell shows "Value#"
at the moment I get in totals cell 59.30 When I m after should be 56:00

Hi,

You might try the following formula in P5 (and copy it down). It won't handle every possible thing you can throw at it, but it should handle a good number of them.

PHP Code: 

=SUMIF($B$4:$O$4,$O$4,$B5:$O5)-SUMIF($B$4:$O$4,$N$4,$B5:$O5)-COUNT($B5:$O5)/2*(30/(60*24)) 


The first two formulas account for the standard time calculation you already had.

The third formula -- COUNT($B5:$O5)/2*(30/(60*24)) -- functions by counting the numbers in the row and dividing by two to determine the number of days. It then multiplies that number by 30 minutes (divided by 60*24 to convert it to days, which is the standard Excel method to store times). You will run into issues if you have odd numbers of times.

S