Subtract time spent for lunch

  • Thread starter Thread starter mikex30
  • Start date Start date
M

mikex30

I have a sheet for my time at work. I want to subtract time for lunch.

Col B is "In" to work Col C is "Out" finished work. Everything is on 24
hr clock, no am or pm. I would like a col D "Lunch" in minutes or
fractions of hour.

B is 0800 arrive at work
C is 1700 leave for the day.
D is 0.50 for lunch
E is 8.5 hours worked

Again I want everything to be on 24 hour clock.

I copied this formula from a template. =(C8-B8+(C8<B8))*24
The templates I found all had out/in for lunch. I just want one col
with the time spent at lunch.


M
 
If you're going to use 0.5 for the lunch period, one way:

=(C8-B8+(C8<B8))*24-D8

Note: while it's more obscure, you can use the equivalent MOD function
instead:

=MOD(C8-B8,1)*24-D8
 
I use (HOUR(E15-D15)+(MINUTE(E15-D15)/60))-(HOUR(F15)+(MINUTE(F15)/60))

This formula converts the time to decimal equivalent. The cell is formated
as accounting with 2 decimal places.

Jerry
 
Back
Top