3

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?

2
  • It looks like he wants d:hh:mm:ss, although I really question including the seconds. Also, I'd recommend against a colon separating day and time.
    – Dane
    Mar 8, 2013 at 20:26
  • @Dane: Thanks, that's more or less what I did in an update to my answer, although I actually didn't notice your comment until afterwards.
    – martineau
    Mar 8, 2013 at 20:37

2 Answers 2

4

I think this formula will work:

=IF(B1>A1, TEXT(B1-A1,"-h:mm:ss"), TEXT(A1-B1,"h:mm"))

But only for time differences less than 24 hours. For longer periods you could use:

=IF(B1>A1, "-",) & TEXT(INT(ABS(B1-A1)), "#,##0") & "d " & TEXT(MOD(ABS(B1-A1), 1), "hh:mm")

for a result like this (I right justified the remaining time):

DueDate             Now                 Remaining Time
03/01/2013 17:00    03/08/2013 12:16         -6d 19:16
03/07/2013 17:00    03/08/2013 12:16         -0d 19:16
03/08/2013 12:16    03/08/2013 12:16          0d 00:00
03/15/2013 17:00    03/08/2013 12:16          7d 04:44
03/31/2013 17:00    03/08/2013 12:16         23d 04:44
05/08/1944 12:00    03/08/2013 12:16    -25,141d 00:16
06/14/2238 07:00    03/08/2013 12:16     82,276d 18:44
03/08/2013 09:00    03/08/2013 12:16         -0d 03:16
03/08/2013 17:00    03/08/2013 12:16          0d 04:44

It could probably be expanded to handle years, too, if necessary. Doing that with total accuracy would be a bit trickier though, since the number of days in a year varies1 -- so doing that will be left as an exercise for the reader. ;-)

[1] According to Wikipedia, the average calendar year is 365.2425 days long.

9
  • 1
    You’ve got a typo: you’re not supposed to be subtracting A2 minus A3; you’re supposed to be subtracting A2 minus B2. Also, you can simplify this in a couple of ways. One is to eliminate the ABS function by understanding what it does: =IF($B2>$A2, TEXT($B2-$A2,"-h:mm:ss"), TEXT($A2-$B2,"h:mm:ss")). The other is to keep the ABS function but eliminate the redundancy: =IF($B2>$A2, "-", ) & TEXT(ABS($A2-$B2),"h:mm:ss"). Mar 8, 2013 at 18:50
  • @scott: Thanks for pointing out the typo in the formula - I was rushing and copied the wrong cell...and likewise for the somewhat obvious optimizations.
    – martineau
    Mar 8, 2013 at 19:20
  • The days formula doesn't show negative values, though. Perhaps use this - =IF(A1>B1,"-"," ")&INT(ABS(B1-A1))&"d "&TEXT(MOD(ABS(B1-A1),1),"hh:mm") Mar 8, 2013 at 21:03
  • 1
    Guys: (1) Why wrap yourselves around the axle getting the fractional part of B1-A1, by MOD or otherwise? I believe that’s unnecessary; I believe that TEXT(x, "hh:mm") is always the same as TEXT(MOD(x,1), "hh:mm"), at least for x ≥ 0. (2) Why even go to all that much bother? Just do =IF(B2>A2,"-",) & TEXT(ABS(B2-A2), "d""d"" hh:mm"). Note: the last string is «d"d" hh:mm», with the internal quotes doubled. And @Barry: What’s the big idea of reversing the direction of the sign test? Mar 8, 2013 at 22:20
  • 1
    @Scott - guilty as charged on all counts!...only thing I would say is that "d" format only goes up to 31 (because it's really taking the day of the month rather than elapsed days) so if periods are possibly > 31 days then the extra INT part may be required Mar 8, 2013 at 23:47
-1

If you want excel to display negative times, even when doing calculations with them, you can change how the times are created. You can do this by going to tools, Options, Calculations Tab and selecting the 1904 Date System. This will now display negative times as -xx:xx:xx

1
  • The OP has stated that he would prefer not to use the 1904 date system
    – Tog
    Jul 10, 2013 at 7:52

You must log in to answer this question.

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