0

I am making a scheduled events in Excel 2016 that tracks the difference between a series of date/time stamps and keeps a running total.

... |    ETA       |     ATA       |     Diff     |     Total          |  ...
     11/20/18 8:00   11/19/18 12:00  (ETA - ATA )   (diff + last diff)

The problem is that Excel doesn't handle datetimes that are negative. So if something is ahead of schudle, it becomes a #VALUE and the whole series dies.

I've tried using 1904 date format but that always is at least 1 day and plus there's no zero date and even equal datetimes will equate +1 days.

Then there's using TEXT() to convert to text but then it not really possible to use it for the next event since its not longer a date and negative dates will still be an issue.

Only other option is to break out days/hours/minutes into separate hidden columns and convert/add to each date for each datetime or something silly.

Is there some other way of keeping a running total of positive or negative datetimes that I am missing?

0

Since Excel represents date/time values as Double, it's straightforward math of course. I suspect your issue lies with the formatting of your Diff column. In this example:

|         A        |         B        |      C       |    D     | ... |   AA   |
|        ETA       |        ATA       |     Diff     |  Total   | ... | Helper |
|------------------|:----------------:|:------------:|:--------:| ... |:------:|
| 11/20/2018 8:00  | 11/19/2018 12:00 |   00:20:00   |          | ... | =A2-B2 |
| 11/19/2018 12:00 | 11/20/2018 8:00  |  -00:20:00   | =AA2+AA3 | ... | =A3-B3 |

The Diff column is not formatted as a Date, but rather as General. The formula in the Diff column is

=TEXT((A2-B2)*24,"#0.00")&" hours"

But of course you can adjust that to whatever you need.

Expaning on the answer above, use a "helper" column (even if it's way to the right)

So instead, in the Diff column, use a formula such as

=IF(A2>=B2,TEXT(A2-B2,"dd:hh:mm"),"-"&TEXT(B2-A2,"dd:hh:mm"))

this will give you a nicely displayed value. But then out in a far-right column (maybe AA?), use the simple formula =A2-B2. Then back in your Total column, it can perform the running total to include positive and negative numbers.

|improve this answer|||||
  • I was formatting the Diff and Totals as dd:hh:mm to show the time in a 'date-y' format. The trick with with putting it into TEXT the running totals won't be able to be added with "hours" on there. – E.F. Nov 29 '18 at 20:50
  • I updated my answer with some additional ideas that should help. – PeterT Nov 29 '18 at 21:08
  • Thanks for updating. I also overlooked the possibly of not using a 'date-y' format and just keep it +/- days in number format. Then at the end of the series, add it to an absolute datetime and it'll never yield a negative date. – E.F. Nov 29 '18 at 22:25

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.