• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Subtracting time (dealing with negative values)

The first tab in the attached file has a formula that shows how to deal with negative numbers when subtracting time. I got this from a video on YouTube.

Cell D11 would result in a negative value. In order to correct for this, they are using a comparison operator in the formula and adding that to a time value. How can time be added to a TRUE/FALSE value?

How does this comparison quantity correct for negative time values?

--------------------------------------------------------------------------------------------------------------------------------

The second tab uses the NOW( ) to take the current time, then take the difference between the current time and some future time (example 1). In example 2, the NOW( ) takes the current time and calculates the difference based on a previous time in the day.

As of this posting Example 1 calculates just fine, but Example 2 doesn't.

I'm trying to come up with one formula that I can use in both cases. I thought the MOD( ) function would help, but it's not working the way I thought it would.
 

Attachments

  • Subtracting Time.xlsx
    11.3 KB · Views: 11
Last edited:
The TRUE/FALSE is coerced to a number when used in any arithmetic operation
(D7>C7)+0 will return 1 or 0
Multiplying by 1 will give the same result, as will the unary operator --
 
The picture does not reveal the real value, so are you sure 10 AM is of the same day? I'm guessing it is not.

EDIT: saw too late there was an original file containing the data from the screenshot.

As I understand it, when the current time is passed the done by, it takes the remaining until that hour the next day.
[D24]=15:03:00
[E24]=D24+TIME(18;57;0) = 10:00:00

If you need the calculation for the same day, it could be =MAX(0,D21-MOD(NOW(),1)). This returns 0, when there is no remaining time left. But I'm not sure that is what you want.
 
Last edited:
Back
Top