Forum Discussion
Formula occasionally returning 0:00 and -0:00 instead of blank
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.
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"))
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.
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:
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.
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"))
