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"))
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:
https://1drv.ms/x/s!ArCIxIiMekBZlAef6jJ53CQcl8LO?e=cTvanc
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"))
- 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.
