Calculating amount of lunchbreak

B

barc0de

I am calculating weekly hours for an optometrist.

If no lunch break is taken a mandatory 30 minute lunch break needs t
be calculated.

Once the amount of the lunch break is calculated is there an if,the
statement to help me out with this problem?

Example: Some may take a 15 min break and some may take a full hour.

Thanks in advanc
 
L

lynxbci

Assuming that the cell with the lunch break is B2 then you can use

=if(B2>0,B2,30)

This states that if the cell has no time in it then use 30 as a defaul
value.

Hope this help
 
B

barc0de

Thanks lynxbci,

This is great if the time entered is 0, but does not help if the tim
is anwhere from 1-29 minutes
 
J

Jason Morin

With start time in column A, end time in column B, and the
lunch time (in min.) in col. C, try:

=(B1-A1-MAX(1/48,C1/1440))*24

For example,

start end lunch total hrs
8:00 AM 5:00 PM 45 8.25
7:30 AM 6:00 PM 15 10

HTH
Jason
Atlanta, GA
 
B

barc0de

Well, I would like a formula that does this:

Minimum break is 30 minutes, but some will take over an hour.

So I would like a formula that says if the amount of time is under 3
minutes, then substract it from 30
 
L

lynxbci

So lets try

=IF(B2>0,IF(B2<30,30-B2,0),0)

this will leave 30-B2, so if you have a 10 min break it will leave 2
mins, but if you go over 30 mins it will display
 
B

barc0de

=IF(B2>0,IF(B2<30,30-B2,0),0)

This formula works great except when the time is 0

Can you modify it to say 30 when the time is 0?

Thank
 
Top