ISBLANK displaying 0 for an empty timed format linked cell in Google Sheets

Question

I have an ISBLANK function being used on a cell that contains a minute and second format (MM:SS).

Even though the linked cells are blank in the range C7:C16, the ISBLANK value returns 0 when it should be blank. The latter part of the formula calculation that shows seconds may perhaps be interfering with the ISBLANK formula.

Here is the Google Sheet, you can make a copy at File-Make a copy

https://docs.google.com/spreadsheets/d/1V-nL8S4M7B54ZLUl2FTWCxaJMlAz-LF2nY_HoxNYji4/edit?usp=sharing

Answer 1

Tl;Dr: Remove ""

---

Current formulas

B7

=IF(ISBLANK(A7),"",LEN(A7)/18.2/86400)

C7

=IF(ISBLANK(B7),"",(((HOUR(B7))*60+(MINUTE(B7)))*60 +SECOND(B7)))

Resulting formulas

B7

=IF(ISBLANK(A7),,LEN(A7)/18.2/86400)

C7

=IF(ISBLANK(B7),,(((HOUR(B7))*60+(MINUTE(B7)))*60 +SECOND(B7)))

Answer 2

The issue is that any of the HOUR, MINUTE, SECOND functions will default to 0. eg.=HOUR("")

An alternative is to use ISNUMBER instead. You could also wrap your formulas in an ArrayFormula.

For column B please try the following:

=ArrayFormula(IF(ISBLANK(A2:A),"",LEN(A2:A)/18.2/86400))

In column C try

=ArrayFormula(IF(ISNUMBER(B2:B),(((HOUR(B2:B))*60+(MINUTE(B2:B)))*60 +SECOND(B2:B)),))