How can I handle negative time values?

[Guest]

I have a spread sheet that I use for time comparisons on a project. There
are target and actual times that get compared. I want to be able to show the
resulting difference as a positive or negative value. For example:

Target Time: 9:00 AM
Actual Time: 9:01:25 AM

I would like to be able to show tha result as + 01:45

I also need to account for the other situation as well:

Target Time: 9:00 AM
Actual Time: 8:58:25 AM

I would like thsi result to show as - 01:35

Any help would be most appreciated. I can definitely get the calaulation to
run ok, but I have not yet found a way to format the result in the desired
fashion.

dknorwood

 

[Ron Rosenfeld]

I have a spread sheet that I use for time comparisons on a project. There
are target and actual times that get compared. I want to be able to show the
resulting difference as a positive or negative value. For example:

Target Time: 9:00 AM
Actual Time: 9:01:25 AM

I would like to be able to show tha result as + 01:45

I also need to account for the other situation as well:

Target Time: 9:00 AM
Actual Time: 8:58:25 AM

I would like thsi result to show as - 01:35

Any help would be most appreciated. I can definitely get the calaulation to
run ok, but I have not yet found a way to format the result in the desired
fashion.

dknorwood

If you can live with it:

Tools/Options/Calculations

Under Workbook Options SELECT the 1904 Date system.

Then format as mm:ss (or perhaps [h]:mm:ss.0) depending on how much precision
you wish to display.

This will change dates already entered in this workbook by 4 years (and a day),
but will allow you to display negative times.

Otherwise you would only be able to display the value as a text string -- that
would make further mathematical operations difficult. You might be able to get
around this issue by using hidden cells for the actual result, and visible
cells for the displayed result.
--ron

 

[T. Valko]

You can use the 1904 date system to display negative time, however, this
will affect any dates you already have entered in your file. They will be
off by 4 years (1462 days). This can be a significant drawback! You can fix
this side effect but it may not be worth the trouble.

Tools>Options>Calculation>1904 date system

Then you'd have to subtract 1462 days from the dates that changed.

Other options:

Use decimal values instead of time formatted values.
You can display a negative time as TEXT but this may affect any downstream
calculations.

 

[Harlan Grove]

I have a spread sheet that I use for time comparisons on a project. There
are target and actual times that get compared. I want to be able to show
the
resulting difference as a positive or negative value. For example:

Target Time: 9:00 AM
Actual Time: 9:01:25 AM

I would like to be able to show tha result as + 01:45

That should be + 1:25.

I also need to account for the other situation as well:

Target Time: 9:00 AM
Actual Time: 8:58:25 AM

I would like thsi result to show as - 01:35

....

If you don't want to change your date base from 1900 to 1904, you could
achieve this using text formulas, specifically,

=IF(TargetTime<=ActualTime,"+ ","- ")
&TEXT(MAX(TargetTime,ActualTime)-MIN(TargetTime,ActualTime),"[m]:ss")

If you then need to sum these time difference values, which I'll assume are
in a range named Delta, and display the result in the same format, try

=IF(SUMPRODUCT(2-FIND(LEFT(Delta,1),"- +"),-MID(Delta,3,12))>0,"+ ","- ")
&TEXT(ABS(SUMPRODUCT(2-FIND(LEFT(Delta,1),"-
+"),-MID(Delta,3,12))),"[h]:mm")