This formula in A1 properly displays 2004
=YEAR($AI$2)+INDEX(YEARS,MATCH(INDEX(SICK,MATCH(INDEX(SICK,MATCH(TRUNC(((DATE(Base,12,31))-$AI$2)/365),YEARS)),SICK)+1),SICK,0))
This formula in A2 properly displays 01-01-04
=DATE(A1,1,1)
When I replace A1 in the formula above with the actual formula from A1, I get an error with DATE just after ((( highlighted. Why won't this work?
=DATE((YEAR($AI$2)+INDEX(YEARS,MATCH(INDEX(SICK,MATCH(INDEX(SICK,MATCH(TRUNC(((DATE(Base,12,31))-$AI$2)/365),YEARS)),SICK)+1),SICK,0))),1,1)
=YEAR($AI$2)+INDEX(YEARS,MATCH(INDEX(SICK,MATCH(INDEX(SICK,MATCH(TRUNC(((DATE(Base,12,31))-$AI$2)/365),YEARS)),SICK)+1),SICK,0))
This formula in A2 properly displays 01-01-04
=DATE(A1,1,1)
When I replace A1 in the formula above with the actual formula from A1, I get an error with DATE just after ((( highlighted. Why won't this work?
=DATE((YEAR($AI$2)+INDEX(YEARS,MATCH(INDEX(SICK,MATCH(INDEX(SICK,MATCH(TRUNC(((DATE(Base,12,31))-$AI$2)/365),YEARS)),SICK)+1),SICK,0))),1,1)