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
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
ishh:mm
.
N.B.
- If you subtract decimal representation of TIME, are in
Q
&S
Columns, you get4: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.