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

ehrynabbott

New Member
Joined
Mar 27, 2011
Messages
2
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

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
[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
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,237
Members
448,555
Latest member
RobertJones1986

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top