# How to convert negative decimal to negative time in excel and show it

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.

• Thankyou Rajesh, This helps a lot – Jarn Dechert Oct 1 '19 at 21:31
• Gld to help you @JarnDechert ,, keep asking ☺ – Rajesh S Oct 2 '19 at 5:13

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.

• Yes it's one option to fix the issue +10 ☺ – Rajesh S Oct 1 '19 at 5:48