I am trying to send up a calculated cell as duedate - now()
in order to quantify overdue vs. time remaining (negative values = overdue, positive values = time remaining). Consider:
Col A DueDate Col B NOW Col C remaining time
Row1 3/7/2013 9:00 3/8/2013 12:00 -1:03:00:00
Row1 3/8/2013 15:00 3/8/2013 12:00 0:03:00:00
Row3 3/9/2013 9:00 3/8/2013 12:00 1:03:00:00
However,
- This sheet links to a lot of other sheets using the 1900 date system, so I'm leery about changing to the 1904 system because of the unpredictability of how dates will be interpreted between the two date systems.
A1-B1
yields##################
, as we expect when the date returns a negative date.- I did find a text conversion,
TEXT(ABS(A1-B1),-h:mm:ss
, but because it's simply adding a negative to the absolute value, EVERY date value is returned as negative.ABS
is correct, just not the sign.
Using that as a clue, I tried TEXT((A1-B1),h:mm:ss
but got VALUE!#
; I am assuming that it doesn't like the negative result of time-math.
This: IF(B1>A1,(TEXT(ABS(A1-B1),-h:mm:ss,A1-B1)
has a few issues.
- Anything due today is returned as
0:00:00:00
; it appears to be be ignoring hours, minutes and seconds. - Sorting the column on the time appears to sort on
ABS
so my '0:00:00:00' values are at the top of the list and the largest negative numbers at the bottom. I would expect that a normal sort would have the largest negative, then the zero, then ascending to the hightest value.
EDIT:
Martineau's suggestion got me closer, but anything due in the next day or two is squirrely. I did modify the h
to [h]
to account for times greater than 24 hours.
DueDate NOW()
3/1/2013 17:00 3/8/2013 12:16 -163:16:11
3/7/2013 17:00 3/8/2013 12:16 -19:16:11
3/8/2013 12:16 3/8/2013 12:16 992196:16:11 <-- ???
3/15/2013 17:00 3/8/2013 12:16 992369:00:00 <-- ???
3/31/2013 17:00 3/8/2013 12:16 992753:00:00 <-- ???
5/8/1944 12:00 3/8/2013 12:16 -603384:16:11
6/14/2238 7:00 3/8/2013 12:16 2966839:00:00
3/8/2013 9:00 3/8/2013 12:16 -3:16:11
3/8/2013 17:00 3/8/2013 12:16 992201:00:00 <-- ???
However, with the h
last as an h
, the same lines are returning sqirrely values:
DueDate NOW() 3/1/2013 17:00 3/8/2013 12:23 -19:23:45
3/7/2013 17:00 3/8/2013 12:23 -19:23:45
3/8/2013 12:23 3/8/2013 12:23 12:23:45 <-- ???
3/15/2013 17:00 3/8/2013 12:23 17:00:00 <-- ???
3/31/2013 17:00 3/8/2013 12:23 17:00:00 <-- ???
5/8/1944 12:00 3/8/2013 12:23 -0:23:45
6/14/2238 7:00 3/8/2013 12:23 7:00:00
3/8/2013 9:00 3/8/2013 12:23 -3:23:45
3/8/2013 17:00 3/8/2013 12:23 17:00:00 <-- ???
Is there a more elegant way to format column C the way I'd like to see it?