Negative Time Values

[B. Baumgartner]

I have a cell into which I would like to input a time value, either negative or positive from which other cells will perform calculations.

When I enter the time as a positive value it works fine, however if I attempt to put the value in as a negative, i get a popup that says "The formula you typed contains an error. If you are not trying to enter a formula avoid using the - sign."

If I format the cell as -h:mm, it has no problem processing the value entered, however I cannot enter a positive value. This same error occurs no matter how the cell is format, from General, to Number, to Time, to Custom Time.

I have the 1904 date system enabled if that helps with the problem solving.

[Harald Staff]

There is no easy userfriendly way to enter negative time. This will work:
-"04:00"
and this
=-TIME(4,0,)

Formatting as -h:mm will just display a - in front of the positive number.
Formatting does NOT change value, so the cell will still contain a positive
number.

I'd use a small macro that change sign in the selected cell(s):

Sub ChangeSign()
Dim Cel As Range
On Error Resume Next
For Each Cel In Intersect(Selection, ActiveSheet.UsedRange)
If Cel.HasFormula = False Then Cel.Value = -Cel.Value
Next
End Sub

HTH. Best wishes Harald

"B. Baumgartner"
<[email protected]> skrev i melding
news:[email protected]...
>
> I have a cell into which I would like to input a time value, either
> negative or positive from which other cells will perform calculations.
>
> When I enter the time as a positive value it works fine, however if I
> attempt to put the value in as a negative, i get a popup that says "The
> formula you typed contains an error. If you are not trying to enter a
> formula avoid using the - sign."
>
> If I format the cell as -h:mm, it has no problem processing the value
> entered, however I cannot enter a positive value. This same error
> occurs no matter how the cell is format, from General, to Number, to
> Time, to Custom Time.
>
> I have the 1904 date system enabled if that helps with the problem
> solving.
>
>
> --
> B. Baumgartner
> ------------------------------------------------------------------------
> B. Baumgartner's Profile:
http://www.excelforum.com/member.php...o&userid=23107
> View this thread: http://www.excelforum.com/showthread...hreadid=383776
>