Forum Discussion
Formula occasionally returning 0:00 and -0:00 instead of blank
raczoliver wrote: ``I figured out the -0:00s are there because I enabled the use of 1904 date system``
Oh yes, I forgot about that. I hope you stop doing that. It can cause problems if you copy data from one workbook to another.
EDIT.... Unless you use a Mac, in which case I've read that 1904 is the default date system.
-----
raczoliver wrote: ``I suppose these few examples should be enough to troubleshoot my problem``
See the image below and the "corrected" worksheet in the attached Excel file for details.
Apparently, the values in column A, B and E are the results of unrounded calculations, which you copy-and-pasted-value into the example Excel file.
This causes infinitesimal differences between the displayed time and the actual binary value.
That is demonstrated by the formulas that are highlighted in "red" (pink) in columns G, H and K. For example:
G1: =SUM(A1, -TEXT(A1,"[h]:m"))
(I use SUM instead of simply =A1 - TEXT(A1,"[h]:m") in order to avoid a misleading "feature" of Excel arithmetic in the latter form.)
Each of the original formulas in those cells should be explicitly rounded to the minute. For example:
A10: =--TEXT(A1, "[h]:m")
B10: =--TEXT(B1, "[h]:m")
E10: =--TEXT(E1, "[h]:m")
Of course, where I reference a cell (A1), you would use that formula in A1 itself, replacing the formula =expression with =--TEXT(expression, "[h]:m").
The original formulas in column C can be simplified. For example:
C10: =IF(A10=B10, "", --TEXT(B10-A10+(B10<A10), "[h]:m"))
Then, the formulas in column D can be, for example:
D10: =IF(C10<=E10, "", --TEXT(C10-E10, "[h]:m"))
I did indeed copy-paste earlier entries from a document provided to me by someone else before I started manually entering the times myself. What does puzzle me a little is that deleting the problem entries and manually typing in the times only made the 0:00s disappear in some cases, but not in others.
raczoliver .... I wrote: ``But the actual binary values are different, due to binary arithmetic anomalies. This is demonstrated by the different decimal approximations of the binary values in H3 and W3, to wit: 0.715277777781012 and 0.715277777773736.``
That takes for granted some things that you might not be aware of. Forgive me if the following is TMI and something that you know already.
-----
First, Excel dates and times are stored as a decimal number.
The integer part represents a date as a number of days since 12/31/1899. So, 1 is 1/1/1900. 10/1/2023 (Oct 1) is 45200.
But in general, the integer part is just a number of days. So, there is no numerical difference between 12 days and the date 1/12/1900 (Jan 12).
The fractional part represents time as a fraction of a day. So, 1 hour is 1/24; 1 minute is 1/1400 (1400 = 24*60); 1 second is 1/86400 (86400 = 24*60*60); and 1 millisecond is 1/86400000.
But Excel can store time with greater precision. See below.
-----
Second, contrary to most online documentation (including MSFT's), decimal numbers are not stored in memory with ``15 significant [decimal] digits of precision``.
Instead, Excel stores numbers in a binary form. Decimal values are converted to a sum of 53 consecutive powers of 2 ("bits").
Because of that limitation, most decimal fractions cannot be represented exactly. They are approximated.
Likewise, integers greater than 9,007,199,254,740,992 (2^53) must be approximated. Note that that is 16 digits, not 15.
Moreover, the precision of the approximation of a particular decimal fraction might vary, depending on the magnitude of the value. That is the cause of most "binary arithmetic anomalies".
For example, 10.1 - 10 = 0.1 is FALSE(!) because the approximation of 1/10 in 0.1 differs from the approximation of 1/10 in 10.1, which has fewer bits (powers of 2) for the fractional part.
-----
Third, cell formatting only affects the appearance of numeric values.
For example, we might enter the time 12:34:56.789. It might be formatted as 12:34 (h:mm), 12:34:57 (h:mm:ss), 12:34:56.8 (h:mm:ss.0) or 0.524268391203704 (Number).
(Note that Excel rounds seconds and fractional seconds, but it truncates minutes and hours after rounding seconds.)
Regardless of appearance, the underlying binary value is the same, which can be represented in decimal as exactly 0.52426839120370372082646781564108096063137054443359375, highlighting the first 15 significant digits.
Excel displays (formats) decimal numbers only up to 15 significant digits, rounded, replacing any digits to the right with zeros.
Excel displays time at most only to 1 millisecond precision (0.001 seconds).
-----
Finally, when we enter decimal numbers manually, Excel interprets only the first 15 significant digits, replacing any digits to the right. Effectively, that truncates the value after 15 significant digits.
Contrary to most online documentation, that is not a limitation of the internal binary form (64-bit binary floating-point) or its standard (IEEE 754). In fact, VBA interprets all digits, which can improve the accuracy of numbers with 17 significant digits or more.
Similarly, when we enter time, Excel rounds to the millisecond. So, for example, if we type 12:34:56.78954321, that is interpreted as 12:34:56.790. That binary value is different from the binary value for the calculated time "12:34" + 56.78954321/86400.
-----
I hope that is helpful, and not redundant.