Formula occasionally returning 0:00 and -0:00 instead of blank

[raczoliver]

Hello! I am trying to subtract the time in column W from the time in column H, and enter the result into a third column. The time in column H is always greater than or equal to the time in colum W, and all time inputs are only accurate to the minute, seconds are always :00 and not displayed. The formatting of all three columns is [h]:mm.

Since I would like to have blank cells instead of 0:00, I am using the furmula =IF(H2>W2,H2-W2,""). I get mostly blank cells when I'm supposed to, but some rows still return 0:00. I have also tried =IF(H2<>W2,H2-W2,""), =IF(H2-W2=0,"",H2-W2), and IF(H2=W2,"",H2-W2) but in these cases in addition to 0:00, I even get some -0:00 values. Thanks for any input.

[#2]

@raczoliver  wrote: ``I even get some -0:00"

For my edification, please provide an Excel (not an image) that demonstrates this.

Remove any private infomation.  We just need the formulas and constants in H2, W2 and any precedent cells that are referenced.  Upload the Excel file to a file-sharing website (e.g. box.net/files; dropbox.com; onedrive.live.com) or, preferably, click "Drag and drop here or browse files" near the bottom of the reply pane.

AFAIK, Excel cannot display -0:00, unless you outputing that as a string.

-----

@raczoliver  wrote:  ``Formula occasionally returning 0:00 [....] I am using the furmula

=IF(H2>W2,H2-W2,"")``

I don't believe that can happen if H2 and W2 are entered manually (not copy-and-pasted), and they are accurate to the minute.

But any time calculation in H2, W2 or the cells that they reference is subject to binary arithmetic anomalies.  In that case, H2 and W2 might display the same time accurately to the minute (or even millisecond), but their internal binary values might differ.

Thus, H2>W2 might be true based on the binary value, but H2-W2 might display 0:00 because the non-zero positive calculation rounds to 0:00 when formatted as [h]:mm.

For example:

The time differences calculated in H2 and W2 appear to be the same, even when rounded to the millisecond.  But I2 still displays 0:00.

The formula in I2 is =IF(H2>W2, H2-W2, "") formatted as [h]:mm;; .

Note that the format suggested by @Patrick2788  fails to hide the apparent "zero" time in I2 because, in fact, the binary value is not zero, as demonstrated by the rounded decimal value in I3.

The remedy is to round time calculations (any non-integer calculation) to the precision that you expect to be accurate.

In this case, use the following formula in H2 and W2 to round time to the minute:

=--TEXT(G2-F2, "[h]:m") formatted as [h]:mm

The double-negation ("--") converts text to numeric Excel time.

[#3]

@JoeUser2004 

Thanks for the replies. In the meatime I figured out the -0:00s are there because I enabled the use of 1904 date system in the options when I was trying to solve the issue.

Here is a link to a file on Onedrive:

Logbook edit - Copy.xlsx

I deleted irrelevant data, and only left a couple of the problem entries. There's more, but I suppose these few examples should be enough to troubleshoot my problem. Now colum D is supposed to be C-E. C in itself is B-A if B>A, and B-A+1 if B<A. This is because A is a start time, B is the end time, C is the total duration, and I need to account for the possibility that the start time is late evening, and end time is early AM.

Thanks for the help.

[#4 · Accepted solution]

@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"))

[#5]

Thank you, I managed to correct my document with your help by rewriting the formulas. Not sure I completely understand what "--TEXT" does, so far it has not caused any issues with cells referencing those values, I hope it stays that way.

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.

[#6]

@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?

[#7]

@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.