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:

enter image description here

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.


  • 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.

|improve this answer|||||
  • 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.

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.