Excel Time Calculations

test

Board Regular
Joined
Apr 17, 2002
Messages
182
How do I subtract 0:30am (next day) from 22:30pm? Thanks!
This message was edited by test on 2002-04-18 15:55
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
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
 
Upvote 0
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?!
 
Upvote 0
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<A2)-A2

Custom format the cell of this formula as:

[h]:mm
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,483
Messages
6,113,919
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top