Finding difference of two zero times yields

[luggw1]

I have a time sheet where I have a cell that finds the difference of the end time and the start time. If the times are zero, the difference cell is filled with “#VALUE!”. FWIW, the format of the difference cell is ‘[H]“h” MM"m"’, but this is not the cause of the error. How can I insert a formula, say using IIF, that would blank the cell or insert a zero if the difference is zero. I’m also trying to mitigate the issue of the sum of these cells not working if any of them contain this error, so blanking them or inserting a 0 would solve that problem.

Thanks
Bill Lugg

ExampleWorkbook.ods

[erAck]

What is the actual formula used to calculate the difference?

[luggw1]

It’s simply “=$C4-$B4”.

[luggw1]

I’ve added a file demonstrating the situation and my problem (not). Yesterday, I could consistently produce the error. Today everything works fine. Go figure.

[luggw1]

So, after poking around some more, I think I’e answered my own question. I found first that I was using IIF instead of IF to perform a test which I discovered in incorrect. I also used the formula =IF(ISERROR($C4-$B4),0,$C4-$B4) in the cell D4, for example, it works just as I desire.

Now, the curious thing is if I enter =$C4-$B4=0 in a cell and C4 and B4 both contain 00:00 for their time, I would expect the result to be TRUE, but it’s FALSE, which I assume is why the #VALUE! error is appearing in the first place. If someone could explain this I would be most appreciative.

Thanks
Bill Lugg

[mikekaganski]

You should provide an actual sample document, to enable others to see and test the problem.

[luggw1]

I would, but being new to this forum, I don’t see how to attach a file.

[mikekaganski]

Please edit your question, and there you’ll have a toolbar button with a clip to attach files.

[Lupp]

@mikekaganski: New user; karma=1. 3 or more required.
-1- Someone may upvote the question to privide enough karma.
-2- User may rely on an upload service.

[erAck]

=$C4-$B4=0 in a cell and C4 and B4 both contain 00:00 for their time, I would expect the result to be TRUE, but it’s FALSE, which I assume is why the #VALUE! error is appearing

No to both. If 00:00 is displayed it does not necessarily mean the cell content would be 0. For example the value 1 formatted with HH:MM would display 00:00 as well because it is 1 day since null date at 00:00 clock. But that doesn’t cause a #VALUE! error. More likely one of the cell contents wasn’t numeric but string instead, for which the expression $C4-$B4 yielded the error. Suppressing that with ISERROR() in this case is not good practice as it indicates a data error.

[luggw1]

This is a good point I hadn’t considered. The workbook did not originate with me, but was created buy someone my son works for. My son asked me to make some changes to it to make it more “intelligent”. I did find text strings in the start and end time cells that I thought I’d successfully removed and replaced with numeric times prior to inserting the formula calculating the duration. However, I had to fiddle with them some more to get the display correct and I think I finally purged any vestige of the strings at that point. This would explain why things didn’t work at first, but do now. I will go back and change them as you suggest. Thanks for the help.

[erAck]

See also this FAQ.