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.
 

Some videos you may like

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

mole999

Moderator
Joined
Oct 23, 2004
Messages
10,524
Office Version
  1. 2019
  2. 2016
  3. 2013
Platform
  1. Windows
[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
Joined
Mar 27, 2011
Messages
2
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

Moderator
Joined
Oct 23, 2004
Messages
10,524
Office Version
  1. 2019
  2. 2016
  3. 2013
Platform
  1. Windows
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

New Member
Joined
Dec 14, 2009
Messages
7
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
 

Watch MrExcel Video

Forum statistics

Threads
1,123,323
Messages
5,600,954
Members
414,417
Latest member
Nobu

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
Top