#VALUE! error should return blank cell

[redspanna]

Hi all

If B14 is blank with nothing in the cell, why does this formula return the #VALUE! error?

=IF(ISBLANK(B14),"",C14-B14)-TIME(0,30,0)

it should return a blank cell ?

TIA

 

[bartmaster]

The first part of the formula evaluates to blank.
The second part of the formula is is subtracting given time from blank which leads to negative value.
Function Time does not support negative values, hence error.

 

[redspanna]

The first part of the formula evaluates to blank.
The second part of the formula is is subtracting given time from blank which leads to negative value.
Function Time does not support negative values, hence error.

Thanks for explaining, can you suggest an alternative formula that wouldn't return a #VALUE! error then?

Thanks

 

[bartmaster]

Not sure what you're trying to do with that calculation, but if you want to "skip" calculating and return blank, just wrap your formula with IFERROR function:

=IFERROR(IF(ISBLANK(B14),"",C14-B14)-TIME(0,30,0),"")

 

[bartmaster]

Or just move the paranthesis that would basically mean:
If cell is blank then return blank, else subtract C14 from B14 and subtract 30 mins:

=IF(ISBLANK(B14),"",C14-B14-TIME(0,30,0))

 

[redspanna]

Or just move the paranthesis that would basically mean:
If cell is blank then return blank, else subtract C14 from B14 and subtract 30 mins:

=IF(ISBLANK(B14),"",C14-B14-TIME(0,30,0))

Perfect - thanks a lot and have a wonderful Christmas