Note: I am in the UK so my answer uses dates in the format dd/mm/yyyy
Let's say:
date a
is in cell A1
date b
is in cell B1
, and
date c
is in cell C1
Let's then say you put 31/02/2015
into cell A1
and put =IF(AND(DATEDIF(A1,TODAY(),"Y")>=1,ISBLANK(C1)),TODAY(),DATE(YEAR(A1)+1,MONTH(A1),DAY(A1)))
into cell A2
the formula works for me.
As @Máté Juhász said in the comments, the problem arises when you have something in cell C1
. If that is the case, it will add 1 year to the date in A1 making it 31/02/2016 instead of today's date of 02/09/2016.
Cell C1
can appear blank to you if you put a space into it, but Excel will see the space as data. So for safety, if you want to check a cell is humanly visible as blank by including a space as blank, you need to change the logical test to =OR(ISBLANK(C1),C1=" ")
making the final formula in cell B1
as =IF(AND(DATEDIF(A1,TODAY(),"Y")>=1,OR(ISBLANK(C1),C1=" ")),TODAY(),DATE(YEAR(A1)+1,MONTH(A1),DAY(A1)))
An alternative to =OR(ISBLANK(C1),C1=" ")
, which has no functional difference except reducing the length of the formula by 6 characters, is by using the alternative suggested in the comment, which would be =OR(C1="",C1=" ")
making an alternative final formula in cell B1
as =IF(AND(DATEDIF(A1,TODAY(),"Y")>=1,OR(C1="",C1=" ")),TODAY(),DATE(YEAR(A1)+1,MONTH(A1),DAY(A1)))
ISBLANK(date C))
- do you have really blank cells, or cells with formulas appearing blank?date C=""
is more safe to use.