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

#### ehrynabbott

##### New Member
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.

### Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

#### mole999

##### Well-known Member
[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

##### New Member
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

##### Well-known Member
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

Null is answer or action of false

#### JWilder

##### New Member
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

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,164,682
Messages
5,838,789
Members
430,569
Latest member
sbardelli

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back