Negative Time Formula
Posted by Bob Matthews on March 29, 2000 6:05 PM
According to Excel 97 help, if a time formula produces a negative result, Excel will display ########. Does anyone have a workaround for this problem without converting the hour:minutes format to a decimal number?
For example I am wanting to say subtract 7 hours 10 minutes (7:10:00) from 6 hours 5 minutes (6:05:00) and get a result of - 1 hour 5 minutes (-1:05:00).
Can this be done?
Thanks
Bob Matthews
Posted by ivan moala on March 30, 2000 1:25 AM
Bob
If you are familiar with UDF (User Defined Functions)
Then this may help you. There is one draw back in that the the result cannot be used for further
calculations.
Function NTime(sTm, eTm)
Dim TempTm
TempTm = sTm - eTm
If TempTm < 0 Then
NTime = Format(TempTm, "-hh:mm:ss")
Else
NTime = Format(TempTm, "hh:mm:ss")
End If
End Function
Ivan
