In my workbook, column I contains a formula that returns a date based on the manually entered date in column H (though that is irrelevant to the formula I need). Column K also contains a manually entered date.



I need a formula for column Q that will:
1. See if there is a date in I and/or K.
2. If no date appears in either, return a blank in that row.
3. If there is a date in column K, return that date to column Q plus 12 months.
4. If there is no date in column K, but there is a date in column I, return that date to column Q plus 12 months.

I've tried several formulas, each time I am either able to get the dates to calculate correctly but end up with a #VALUE error in column Q when the column I cell is "blank" because it's reading the formula there, or I don't get the error, but the formula seems to be ignoring the date in column I so that if there is no date in column K, it returns a blank.

Here's what I've tried so far.
=IF(AND(ISNUMBER($I2), ISNUMBER($K2)), IF($K2<>"", $K2+365, IF($I2<>"", $I2+365, "")), "") Returns date in column K, with no error on empty rows, but if I delete the number in column K, it ignores column I and returns a blank.

=IF(AND(ISNUMBER($I2), ISNUMBER($K2)), IF($K2<>"", $K2+365, $I2+365), IFERROR(1/0, "")) Same as above.

=IF(AND($K2="", $I2=""), "", IF($K2<>"", DATE(YEAR($K2)+1, MONTH($K2), DAY($K2)), DATE(YEAR($I2)+1, MONTH($I2), DAY($I2)))) Handles the dates correctly but returns #VALUE error in rows with no input.

I also tried =IF(ISBLANK($K2),IF(ISBLANK($I2),"",EDATE($I2,12)) ,EDATE($K2,12)) Same results as the previous.

Maybe the answer lies in somehow combining the two, but I don't know how.

On Fly:

Fantastic!
Can you explain to me what is different about the formula you wrote that got rid of the error message? I deleted my old formulas but pretty sure at least one version I tried had the ISNUMBER function instead of ISBLANK, and it still didn't resolve the error message issue.
Thanks.

Hi
please take a minute to post a sample sheet ( no pics please) explaining your problem when starting a thread, adding some data and desired results.



The fact there is a formula in a cell does not affect the result of ISBLANK.
BUT when the result of a formula is "" ( the empty text string), ISBLANK returns FALSE, although it looks blank...
To check if a cell is blank, using COUNTBLANK is better. If the cell contains "", COUNTBLANK returns 1.

So instead of = ISBLANK(A1), say, instead use =IF(COUNTBLANK(A1)=0...

__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

In 1st 2 formulas, you checked for ...AND(ISNUMBER($I2); ISNUMBER($K2))...
This expression returns True, when both $I2 and $K2 have numeric values. For all other cases an empty string is returned.
3rd formula worked OK for me! I think for you at least one of cells $I2 or $K2 was not empty (e.g. there was a space entered - I tested the formula, and your 3rd formula returned error in case into any of those cells as space was entered).

Btw, when you want the formula to be ideal, you have to check the result date for it being February 28th or the source date used for it being February 29th.. When this is the case, then formula must return 0th day of March instead, so depending the year being leap one or not, either 29thor 28th day is returned!