Excel Time Calculations

[test]

How do I subtract 0:30am (next day) from 22:30pm? Thanks!

[ This Message was edited by: test on 2002-04-18 15:55 ]

[John McGraw]

On 2002-04-18 15:54, test wrote:
How do I subtract 0:30am (next day) from 22:30pm? Thanks!

[ This Message was edited by: test on 2002-04-18 15:55 ]

use ABS() (absolute value) around the subtraction:

if a1 = 22:30 and a2=0:30

abs(a1-a2)



[ This Message was edited by: John McGraw on 2002-04-18 16:00 ]

[Ian Mac]

On 2002-04-18 15:54, test wrote:
How do I subtract 0:30am (next day) from 22:30pm? Thanks!

[ This Message was edited by: test on 2002-04-18 15:55 ]

22:30 today? tomorrow? yesterday?
is that 0:30 tomorrow minus 22:30 today? that would end up in a negative timevalue?!

[Aladin Akyurek]

On 2002-04-18 15:54, test wrote:
How do I subtract 0:30am (next day) from 22:30pm? Thanks!

[ This Message was edited by: test on 2002-04-18 15:55 ]

I think you mean:

=B2+(B2
Custom format the cell of this formula as:

[h]:mm

[test]

22:00 is today and 0:30 is tomorrow

=B1+(B1 this function really worked, thanks a lot!
Excel beginner like me would use something really long like this =if(b1

[Mark W.]

On 2002-04-19 08:06, test wrote:
22:00 is today and 0:30 is tomorrow

=B1+(B1 this function really worked, thanks a lot!
Excel beginner like me would use something really long like this =if(b1

Aladin, is leveraging Excel's ability to "coerce" one data type into another. =B1
=FALSE+0 is 0
=TRUE+0 is 1

The expression, =B1+(B1
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 ]