Calculate Anniversary dates

[Rupert Bennett]

Is there a formula I could write to calculate the anniversary dates based on a column of hire dates? For example, if the hire date is 2/12/02 in column "A", and this is year 2004, it will show the anniversary date to be 2/12/04 in column "B" and next year it will show 2/12/05. Any help will be greatly appreciated.

 

[Tetra201]

Try this:

=EDATE(C5,D5*12)

 

[nbrenn]

Thank you! Not specifically what I wanted, but it's close enough that I can use conditional formatting to tweak it. Thanks again!

 

[Tetra201]

You are welcome.

 

[nbrenn]

Is there a way to format the Milestone Date Column so that dates only display if the number in the Years Column is equal to 1, 5, 10, etc? Possibly with white text & white fill? Or even to highlight the cell (in Column E; example E5) if the cell in Column D (example, D5) is equal to the desired trigger numbers?

A 1 Last Name	B First Name	C Hire Date	D Years	E Milestone Date
2 Last Name	First Name	06/30/2014	2	6/30/16
3 Last Name	First Name	08/08/2016	0	8/8/16
4 Last Name	First Name	06/09/2014	2	6/9/16
5 Last Name	First Name	06/05/2017	0	6/5/17
6 Last Name	First Name	11/28/2016	0	11/28/16
7 Last Name	First Name	05/15/2012	5	5/15/17
8 Last Name	First Name	12/05/2016	0	12/5/16
9 Last Name	First Name	04/04/2016	1	4/4/17
10 Last Name	First Name	12/12/2016	0	12/12/16

<colgroup><col span="2"><col><col><col></colgroup><tbody>
</tbody>

 

[nbrenn]

Corrections: examples should be row 7 (E7, D7, etc)

 

[Tetra201]

Try applying the following CF rule to range $E$2:$E$10:

=OR($D2=1,AND($D2,MOD($D2,5)=0))

 

[nbrenn]

I'm realizing how much I have to learn about Excel, but I'm not sure how to properly apply that formula, on the most basic level.

 

[Tetra201]

Here is how:

1. Copy the formula from Post #16 to the Clipboard
2. In your worksheet, select range E2:E10
3. Go to Styles --> Conditional Formatting --> New Rule --> Use a formula...
4. Paste the formula into the field "Format values where tis formula is true:"
5. Click the "Format..." button and select a font color and/or fill color
6. Click "OK", "OK"