# Excel - Tracking time +/- between sequence of dates

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?

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.

• 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