MORE HELP WITH TIME FUNCTION

[John Smith]

I have a cell (E11) that has calculated the hours worked
'D11-C11(D11>C11)'. If that amount is greater than 5.5 hours (which it
usually is), I need to subtract a one half hour meal period from the
total. I have tried '=IF(D11-C11>="5:30",D11-C11-"0:30",D11-C11)' but
it does not work. What am I not doing or doing wrong?

 

[Ron Rosenfeld]

I have a cell (E11) that has calculated the hours worked
'D11-C11(D11>C11)'. If that amount is greater than 5.5 hours (which it
usually is), I need to subtract a one half hour meal period from the
total. I have tried '=IF(D11-C11>="5:30",D11-C11-"0:30",D11-C11)' but
it does not work. What am I not doing or doing wrong?

What you are doing wrong is assuming that Excel knows you mean five and a half
hours when you use the string "5:30" in the Boolean expression and not the
string "5:30".

In formulas, it is best to use unambiguous representations.

You could use

TIME(5,30,0)

or

TIMEVALUE("5:30")

or even

--"5:30"

Excel will try to convert the value to time if it is used in a mathematical
expression, so you can get away with "0:30" when you are subtracting it. But
not so in the Boolean expression.




--ron

 

[bobocat]

For you information, 5.5 hours = 1/24*5.5, half an hour = 1/24*0.5
therefore , the formula should be

=if(d11-c11>=1/24*5.5, d11-c11-1/24*0.5, d11-c11)
then set the answer in time format

or
=text(if(d11-c11>=1/24*5.5, d11-c11-1/24*0.5, d11-c11),"hh:mm")