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?

Improve this question

1 Answer 1

Reset to default
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
3
  • 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.
    Commented Nov 29, 2018 at 20:50
  • I updated my answer with some additional ideas that should help.
    – PeterT
    Commented Nov 29, 2018 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.
    Commented Nov 29, 2018 at 22:25

You must log in to answer this question.

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