Excel Midnight time bug and my workaround

[roscoe]

I do a lot of work using time serial numbers in Excel 2007 and found a bug that has been confirmed by another but to date I have seen no one present a workaround quite like mine. I'm interested in the thoughts of others…maybe there's an even easier way.

I have a very complex XY-Graph where multiple events are plotted as a function of time (time is the X axis). The graph has a lot of interactive features that include shifting data points earlier or later (left or right). I have VBA code that will add time to or subtract time from a value. Problem is that many common time intervals is an irrational number (1hr = 0.04166666666667). Given that computers not only deal with rational numbers but do so in binary makes adding time units problematic. Taking a time value and then subtracting one hour from it multiple times (I have an action button that subtracts and hour…moving it more than that takes multiple "presses") to get to midnight does not always yield an integer serial code (midnight by definition in Excel is an integer, the fractional part of the code being the portion of the day past midnight…but everyone here probably knows that).

Here's where the bug comes in. After several (binary) manipulations of time, often I don't get midnight but rather a minuscule fraction before midnight. Within VBA that number gets rounded correctly (assuming the error is less than half a second) to midnight, but if that number get's written to a worksheet, Excel 2007 truncates the fraction rather than rounding it up, leaving an integer that is one days less than it should be. Can't tell you how much time I wasted (a) convincing myself I wasn't doing anything wrong and (b) trying to develop a workaround (testing the value relative to midnight and adjusting, etc…). None of the workarounds were logical or reliable.

I finally came up with a technique that has proven 100 percent reliable (well, I've yet to see it fail…). Instead of writing the time variable directl to the cell, I write the time that has been converted from a string representation of the time. Huh? OK, easier shown than told.

Cell(whatever) = CDate(format(VBA_TimeVariable,"m/d/yy h:mm:ss AM/PM")).

The interior format statement converts the variable that may be ever-so-slightly off (but not enough to alter the time value displayed in VBE) into a complete string down to the second; CDate then converts that string back into the correct serial value of the intended time. The net result is that this essentially rounds the time to the right value correctly, undoing all the incremental errors that built up during the binary math steps. Since I figured this out, I now write all times to worksheets this way, even though it may be set to display completely differently on the sheet.

Haven't had a time error since…

Thoughts?

 

[joeu2004]

I do a lot of work using time serial numbers in Excel 2007 and found a bug that has been confirmed by another but to date I have seen no one present a workaround quite like mine.
[....]
Here's where the bug comes in. After several (binary) manipulations of time, often I don't get midnight but rather a minuscule fraction before midnight.
[....]
I finally came up with a technique that has proven 100 percent reliable
[....]
Cell(whatever) = CDate(format(VBA_TimeVariable,"m/d/yy h:mm:ss AM/PM")).

It is not a bug, but a side-effect of the binary representation of numbers and arithmetic, as you say. I write about it extensively, not that you should know that. The issue is not unique to time calculations. The issue is present with all arithmetic with non-integer values.

The general solution is to explicitly round non-integer arithmetic whenever you expect a result to be accurate to a specific precision.

And yes, IMHO, generally it is best to round time by using TEXT(time,"m/d/yyyy hh:mm:ss") in Excel and Format(time,"m/d/yyyy hh:mm:ss") in VBA.

(However, ironically there is a defect in Excel 2003 such that no form of rounding is entirely reliable.)

By the way, it is not necessary to use CDate() in the context above. And AM/PM is not necessary if you use "hh" as I do instead of "h".

Also, I always advise to enter dates with 4-digit years to avoid. You can format the cell as you wish.

PS.... And the problem is not unique to times around midnight.