how do i deduct 30 min or 1 hour lunch in excel timesheets

[ehrynabbott]

i am building a time sheet with the following info
column "A" employee name
column "B" as "time in"
column "C" as "time out"
then I have a hidden column - column "D" is calculated using the following "=TEXT(C7-B7,"h:mm")"
And that adds up the time worked for the day.

I have this set up Sunday through Saturday and at the end of the week, I have a formula totaling all my hidden columns using
"=SUM(D6+G6+J6+M6+P6+S6+V6)"
Which gives me total worked hours for the week.

My dilemma.... I need to calculate a 30 min lunch hour for the two managers and an hour lunch hour for those working OVER 6 hours in a day.

I have tried "=IF(cell>6,-.30)" but all I get as an answer is "########"
I've even tried to reformat the cells to a different format. Using [hh]:mm
or even "hh:mm"

Thank you for any advice anyone can give.

 

[mole999]

[h]:mm is the normal route for showing over 24 hours

30 mins is 0.020833 decimal
60 mins is 0.041667 decimal

0.3 is actually 7 hours 12 mins

a day is equivalent to 1

I can't get a cell to hold

=IF(cell>6,-.30)
mine converts to
=IF(cell>6,-0.3)

so you looking at =IF(A6>6,-0.3) it could return false if A6 is empty

 

[ehrynabbott]

That worked. However, the cell did return false because say...
on one Sunday one guy may work, but not on the next. So If I want my formulas to stay put and just delete the hours, then it will come out false.

Any suggestions? Should I put 00:00 as time in and time out when they're not at work?

 

[mole999]

the simplest could be

=IF(A6>6,-0.3," ")

depends what you need to do in the future

the A6>6 is what you are testing for

-0.3 is your answer or action if true

Null is answer or action of false

 

[JWilder]

Not elegant, but this formula seemed to work in D7

=IF(B11=0,0,TEXT(IF(VALUE(TEXT(C11-B11,"h:mm"))>0.25,VALUE(TEXT(C11-B11,"h:mm"))-0.041667,(VALUE(TEXT(C11-B11,"h:mm"))-0.020833)),"h:mm"))

Jim