On 2002-04-19 08:06, test wrote:
22:00 is today and 0:30 is tomorrow
=B1+(B1<A1)-A1
this function really worked, thanks a lot!
Excel beginner like me would use something really long like this =if(b1<a1,b1+24-a1,b1-a1), but can you explain how does the formula =B1+(B1<A1)-A1 work? I never knew that you can put "B1<A1" in a formular...what does it do, same as a IF function?
Aladin, is leveraging Excel's ability to "coerce" one data type into another. =B1<A1 produces a boolean value (TRUE or FALSE). When a boolean value is used in combination with an arithmetic operator it is coerced into a 0 or 1. So...
=FALSE+0 is 0
=TRUE+0 is 1
The expression, =B1+(B1<A1)-A1 , simply adds 1 day if the time value in B1 is less that the time value in A1.
Other examples of coercion include...
=1&"" produces "1"
="1/1/02"+0 produces 37257, the date value for January 1st.
="6:00"+0 produces 0.25, the time value for 6:00 AM.
And, while we're discussing the effect of arithmetic operations on boolean values...
I should also mention that the + operator when used in logical expressions is the equivalent of OR. The * operator is the quivalent of AND.
=IF(TRUE+FALSE,1,0) produces 1
=IF(TRUE*FALSE,1,0) produces 0
...just like...
=IF(OR(TRUE,FALSE),1,0) produces 1
=IF(AND(TRUE,FALSE),1,0) produces 0
This message was edited by Mark W. on 2002-04-19 09:28