Problem with ISBLANK() functioning

[Laura Roberts]

[TABLE="class: grid, width: 250"]
<tbody>[TR]
[TD]in[/TD]
[TD]8:00[/TD]
[TD]8:00[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]out[/TD]
[TD]12:30[/TD]
[TD]12:15[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]in[/TD]
[TD]13:30[/TD]
[TD]13:15[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]out[/TD]
[TD]17:30[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]total[/TD]
[TD]8:30[/TD]
[TD]6:37[/TD]
[TD]15:37[/TD]
[TD]15:37[/TD]
[/TR]
</tbody>[/TABLE]
function for the first total (in cell B10)=IF(ISBLANK(B6:B9),"0",IF(ISBLANK(B7:B9),NOW()-B6,IF(ISBLANK(B8:B9),B7-B6,IF(ISBLANK(B9),(NOW()-B8)+(B7-B6),(B9-B8)+(B7-B6)))))

(now()=15:37)

Why is the first section (for all blanks being true) not parsing as 0?

 

[Aladin Akyurek]

The question is not clear.

By the way, if you want a zero (as number) as output, do not double quote it. Thus: 0, not "0".

 

[Laura Roberts]

I tried just "" to leave it blank, putting just 0, and generally anything in there wasn't being passed no matter what it was.

The first column of times is a full day and is parsing correctly. The second is a partial day and is also parsing correctly. The empty day is returning the value for NOW() for some reason, when I want it to just be 0 so I can get a live update of hours in a total at the end (not shown). I want to know why this behavior is happening, whether I'm somehow using the isblank() function incorrectly, or if it's even something as simple as a missing comma somewhere.

 

[jtakw]

Hi,

ISBLANK doesn't work on arrays UNLESS you enter it as an Array formula confirmed with Control, Shift, Enter.

Book1
	A	B	C	D	E
6	in	8:00	8:00
7	out	12:30	12:15
8	in	13:30	13:15
9	out	17:30
10	total	8:30	5:10	0	0
Sheet5

Cell Formulas
Range	Formula
B10	{=IF(ISBLANK(B6:B9),"0",IF(ISBLANK(B7:B9),NOW()-B6,IF(ISBLANK(B8:B9),B7-B6,IF(ISBLANK(B9),(NOW()-B8)+(B7-B6),(B9-B8)+(B7-B6)))))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Laura Roberts]

Is it an array in this case because I've used the range syntax (B6:B9) and similar? And if it's not supposed to work, why do the other sets (such as when one or two are blank) still work?

If it's a problem with being an array, would a possible solution be this:
=IF(AND(ISBLANK(B6),ISBLANK(B7),ISBLANK(B8),ISBLANK(B9)),0,IF(AND(ISBLANK(B7),ISBLANK(B8),ISBLANK(B9)),NOW()-B6,IF(AND(ISBLANK(B8),ISBLANK(B9)),B7-B6,IF(ISBLANK(B9),(NOW()-B8)+(B7-B6),(B9-B8)+(B7-B6)))))

 

[Laura Roberts]

Splitting up the ISBLANK() worked; awesome. Thank you! But why was it sometimes working before?