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 wrote: ``Not sure I completely understand what "--TEXT" does``
Please review my first response. As I explained: ``The double-negation ("--") converts text to numeric Excel time.`` And use the TEXT expression ``in H2 and W2 to round time to the minute``.
Is that clear?
-----
raczoliver wrote: ``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``
Again, I believe I addressed that in my first response. But perhaps I was not clear enough. See the following image and attached Excel file for details.
The example shows times (with dates) that I entered manually in F2, G2, U2 and V2. They are accurate to the minute.
And the time arithmetic in H2 and W2 seems to display the same result, even if we format it to the millisecond, namely 17:10:00.000.
Nevertheless, 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.
Your original formula in I2 is =IF(H2>W2,H2-W2,""), which is formatted as [h]:mm;; per the (deleted) suggestion by Patrick2788. (I would simply format as [h]:mm .)
Since H2>W2 is true, H2-W2 in a nonzero binary value in I2. That is demonstrated by the decimal approximation in I3, namely 7.27595761418343E-12, which rounds to 0.000000000007276.
But I2 displays 0:00 because of the cell format and the binary value represents 0.000000628642738 seconds.
Is that clear?