Subtracting minutes in an +IF formula

[tka8fan]

We want to create a spreadsheet to record start and end times for our
employees but also giving us a total of hours worked excluding the 30 minute
break. For example, an employee is scheduled to start at 8am and leave at
1pm. Using a simple time formula, this would give us 5 hrs, however, we want
to automatically subtract the required 30 min break so that the schedule
reflects total work hours of 4.50 - or 4:30.

I need an =IF formula that would say if end time minus start time is greater
than or equal to 5 hrs, then subtract 30 mins, otherwise give the total of
end time minus start time. If I have all the fields formatted as numbers, I
can make it work using two diff formulas:

Column B = In time
Column C = Out time
Column D = Total hours worked =+C19-B19
Column E - formua subtracting .5 hrs =IF(C19-B19>=5, C19-B19-0.5,D19)

It was easier using two formulas than attempting to do it all in one.
Column E gives me the answer of 4.5 hrs that I want. Now, how can I replace
the formula using time? I even put :30 in a separate column (F) and tried

=IF(C3-B3>=5, C3-B3-F3, C3-B3)

but that doesn't get me the correct answer either. I have even tried doing
the formula as text with no luck. Any ideas from people far smarter than I??

 

[ldiaz]

Name In Time Out Time Total hours worked Formula
J P 8:00 AM 1:00 PM 5.00 4.5
H U 9:00 AM 2:00 PM 5.00 4.5


put this formula in D2
=(C2-INT(C2))*24-(B2-INT(B2))*24
and this in E
=IF(D2>=5,((C2-INT(C2))*24-(B2-INT(B2))*24)-0.5,(C2-INT(C2))*24-(B2-INT(B2))*24)

please advise if worked.

 

[Mike H]

Try

=IF(((C19-B19)*24)>=5,C19-(B19+TIME(0,30,0)),C19-B19)

Mike

 

[Bob Phillips]

=C19-B19-((C19-B19)*24>=5)*TIME(0,30,0)

 

[tka8fan]

I'm sorry, Mr. Diaz, but I could not get your formula to work correctly,
however, the answers provided by Bob and Mike both worked. Thanks so much
for taking the time to respond!!

 

[tka8fan]

Mike: Your formula and the one provided by Bob both worked. Thank you so
very much for taking the time to reply and help solve our problem!

 

[tka8fan]

Mr. Phillips: Your formula and the one provided by Mike both worked. Thank
you so very much for taking the time to reply and help solve our problem!!

 

[tka8fan]

Ah, Mr. Phillips, things were going so well, but now I have hit another
snafu. The formula works perfectly until I get to schedule entries that
cross over midnight. For example, the schedule is showing a start time of
10pm and an end time of 6am. Depending on how I format the cells containing
my formula, I either get an answer of 16 hrs or I get a cell full of #####s.
I can get the correct answer if I enter the 10pm entry as "22:00" and the 6am
entry as "30:00" but I doubt my managers are smart enough to deal with that
solution. Any further wonderful suggestions you might offer? Sara

 

[Bob Phillips]

Yeah, quite easily resolved

=MOD(C20-B20,1)-((MOD(C20-B20,1))*24>=5)*TIME(0,30,0)

 

[tka8fan]

You are a genius and my hero!! I can't make heads or tails of the formula,
but it works; and that's what really matters right now. I can't thank you
enough for your help with this project. YOUR work is going to make ME look
really good!! Just kidding. I promise to give credit where credit is due.
Thank you SO much for taking the time to respond and provide the answers I
needed. I would sure be interested in knowing how you learned all this!
Your devoted fan, Sara.

 

[Bob Phillips]

It doesn't matter about the credit, we have all done it in our time.

One thing I don't get in your requirement is this. If the time is >- 5
hours, we deduct the 30 mins break. But what about a total time of say 4:45?
That stays at 4:45 as it stands, but wouldn't this mean that a break has
been taken, maybe 30 mins, but maybe just 15 mins.

 

[tka8fan]

Any employee working 5 or more hours is required to take the 30 min unpaid
meal break. The 15 min breaks they take every two hours are paid so we don't
need to adjust our actual labor hours for that time. To date, our managers
have been sort of "manually" adjusting their scheduled labor hours for the 30
min since the schedule they were using showed the total hour diff between the
in and out times and could not reflect true work hours. Now, with your help,
it does!! and they will no longer need to adjust for the breaks on the
schedules! Does that make sense?

 

[Bob Phillips]

Yes it does, although I still find it a bit odd that if you work 5 hours, it
reduces to 4:30, but if you work 4:45, it doesn't. But it's your business,
you know how it works <bg>

 

[ddickerson]

It doesn't matter about the credit, we have all done it in our time.

One thing I don't get in your requirement is this. If the time is >- 5
hours, we deduct the 30 mins break. But what about a total time of say 4:45?
That stays at 4:45 as it stands, but wouldn't this mean that a break has
been taken, maybe 30 mins, but maybe just 15 mins.

--
__________________________________
HTH

Bob




How do I get the formula to deduct one hour on some and none on others?