Formula to recognize anniversary dates

[Tanijan]

Hello,

I am creating a simple spreadsheet. Column A will have the names of my company's clients. Column B will list the date they became a client.

My company wants to send out cards and gifts on significant anniversary dates to thank them for their business (every five years).

What formula can I use to display the NEXT significant anniversary date in Column C?

Thank you!!

[JBeaucaire]

You can use the DATE() function to add years to a date if you wish:

=DATE(YEAR(B2)+5, MONTH(B2), DAY(B2))

[alansidman]

Try this

=IF(DATE(YEAR(NOW()),MONTH(B2),DAY(B2))>NOW(),DATE(YEAR(NOW()),MONTH(B2),DAY(B2)),DATE(YEAR(NOW())+1,MONTH(B2),DAY(B2)))

[Tanijan]

Thanks for your post! I was hoping to only display the next upcoming anniversary date and not display anniversary dates that have past. I will later add conditional formatting to column C to highlight the cell if the anniversary date is within 30 days so we know to prepare to send out the gifts and cards.

If the solution requires hiding columns or sheets to accommodate formulas and solutions that would be fine

[Tanijan]

Thanks for your post alansidman! it's better than what i had but it is making all the anniversary dates in 2013???

Any other suggestions?

[alansidman]

It is designed to pick up the next anniversary after today. So if the month of anniversary is May, then 2013 would be the next date. If the anniversary is November, then it will pick up November 2012. If you want to show only 5 year anniversaries, then you will need to add a column and use Jerry's presentation. I have to run out now, but will look at an another solution later. Meantime, someone else may jump in with a better solution.

Alan