MORE HELP WITH TIME FUNCTION

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
Thu Nov 09 21:10:17 CST 2006

On Thu, 09 Nov 2006 21:35:03 -0500, John Smith <toysnivy@ptd.net> wrote:

>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
Thu Nov 09 21:11:52 CST 2006

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")


"John Smith" <toysnivy@ptd.net> ???????:sRucnVMqav8Lec7YUSdV9g@ptd.net...
>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?