anniversary dates

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

How do I get a date in the past (column a) to show as a due date this year in
column b? i.e. if they joined the company on 3-dec-98 then I want the next
column to automatically say that their appraisal is due on 3-dec-07. THANKS
 
Try this:

A1: (Hire date..no future dates, no text)

Next anniversary date:
=IF(A1,TODAY()+LOOKUP(365,--(TEXT(A1,"dd-mmm-")&(YEAR(NOW())+{0,1}))-TODAY()),"")

Does that help?
--------------------------

Regards,

Ron (XL2003, Win XP)
Microsoft MVP (Excel)
 
Actually, I was close....but the formula I posted trips up if the hire date
is 29-Feb of a leap year. It returns an error if the next anniversary date
is not in a leap year.

To return the same values that the EDATE function would return...(29-Feb
becomes 28-Feb in non-leap years...and 29-Feb in leap years)
try this:
=MIN(DATE(YEAR(NOW())+(MONTH(A1)<MONTH(NOW())),MONTH(A1)+{0,1},DAY(A1)*{1,0}))

--------------------------

Regards,

Ron (XL2003, Win XP)
Microsoft MVP (Excel)
 
Back
Top