I'm creating a spreadsheet for dispatch staff to track how far ahead/behind they are of schedule.

Columns C4-16, E4-16 and F4-16 are lists

D4-16 & G4-16 are converting the time from there adjacent columns to decimal

H4-16 is intended to display "Yes" if the trucks are ahead of schedule or ontime "No" If they're behind and "" if nothing is entered. `=IF(I4/24<0,"Yes",IF(I4/24>0,"No",IF(I4/24=0,"")))`

I4-16 is achieved by the subtraction of columns G - D

J4-16 is intended to show time behind "+" and Time ahead "-" by converting I4-16 from decimals to time the formula is `=IF(I4/24<0,I4/24,IF(I4/24>0,I4/24,IF(I4/24=0,"")))`

and the Cell Format `+h:mm:ss;-h:mm:ss;h:mm:ss`

.

I receive error of "#############" on negative time

I've attached a screenshot of the spreadsheet

Negative Time

**This simple method solves the issue:**

*To get results in valid TIME format, I would like to suggest subtract TIME values instead if Decimal.*

Formula in Cell

`T51 & fill it down.`

`=IF(R51-P51<0, "-" & TEXT(ABS(R51-P51),"hh:mm"), R51-P51)`

Applied Cell Format on

`T51:T52`

is`hh:mm`

.

**N.B.**

- If you subtract decimal representation of TIME, are in
`Q`

&`S`

Columns, you get`4:00`

&`-04.00`

.

*Adjust cell references in the formula as needed.*

I actually found an answer to this question if you navigate to **File > Options > Advanced > When Calculating in this Workbook**. Check the box with *"Use 1904 date system"*.

negative time will be displayed.