# Subtracting time (dealing with negative values)

#### DashboardNovice

##### Member
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

• 11.3 KB Views: 10
Last edited:

#### pecoflyer

##### Active Member
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 --

#### pecoflyer

##### Active Member
As of this posting Example 1 calculates just fine, but Example 2 doesn't.
Please explain, it works perfectly for me
Also be aware that the NOW() function is volatile

#### DashboardNovice

##### Member
Please explain, it works perfectly for me
Also be aware that the NOW() function is volatile
Since NOW( ) is a volatile function, I took a screen picture with my comments to show what I was looking for.

#### Attachments

• 12.9 KB Views: 12

#### GraH - Guido

##### Well-Known Member
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: