Leave empty if BLANK formula

Dear Community,

Good Afternoon from sunny but frosty kent, (already got through half a bag of coal today)!!

Please see formulae below:

I would like to add in this case below (if L2 is blank leave blank)

=INT((L2-I2)/30)

and in this case (if J3 is Blank leave blank)

=12*(YEAR(J3)-YEAR(I3))+MONTH(J3)-MONTH(I3)

I have been trying along the lines of =if"","",

Do I need an IF OR or IF AND S'ment to solve this?

Thanks All
Darren

if(l2="","",INT((L2-I2)/30))
if(j3="","",12*(YEAR(J3)-YEAR(I3))+MONTH(J3)-MONTH(I3))

Hey Martin, thanks for your time.
Darren

If you wanted to be **** about it given the explicit coercion taking place it would be best to test that the values are numeric (ie use COUNT)

Please Login or Register  to view this content.

valid "numeric strings" would technically be ok here (ie COUNT not strictly obligatory) but realistically we're not expecting anything other than numbers (true date values)

Hi Don,

Nope, not into ****. Not for all the tea in China!

Will check out your code. Then store it in my "Excel" Tips, Tricks and Help Folder!

Darren

Hi Don, I tried this one Below and got -1340 so i changed the : between (L2:I2) to a , and it seems to work.

[code]
=IF(COUNT(L2:I2)<2,"",INT((L2-I2)/30))

Any idea why?
Cheers
Darren

Yes because what I posted was wrong.

The colon tells XL the range is contiguous (ie L2 To I2) whereas the comma tells XL the ranges are non-contiguous (L2 and I2)
In this instance you have a non-contiguous range (the 2nd example using colon is fine as the two cells of interest are contiguous)

Ahh i see. This one worked with the colon because it is in the next cell. ie contiguous.

what you posted wasnt necessarily wrong as i didnt tell you in which cell i was going to put the Formula in!!

Cheers and sorry to bother you with such minor excel functions! although its a slight relief from P and F type tools!!
Cheers
Don

=IF(COUNT(I3:J3)<2,"",12*(YEAR(J3)-YEAR(I3))+MONTH(J3)-MONTH(I3))