1

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

2 Answers 2

3

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.

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.

2
  • Thankyou Rajesh, This helps a lot Oct 1, 2019 at 21:31
  • Gld to help you @JarnDechert ,, keep asking ☺ Oct 2, 2019 at 5:13
2

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.

1
  • Yes it's one option to fix the issue +10 ☺ Oct 1, 2019 at 5:48

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .