Calculate Anniversary dates

Thanks:  0
Likes:  0

1. Calculate Anniversary dates

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.

To show how many days until a anniversary(anniversary in A1):
=IF(N(NOW()-DATE(YEAR(NOW()),MONTH(A1,DAY(A1))))>=0,(TEXT(NOW()-(DATE(YEAR(NOE()),MONTH(A1,DAY(A1))),"#")+0),(TEXT(NOW()-DATE(YEAR(NOW()+1),MONTH(A1),DAY(A1))),"#"))*-1)
'courtesy of Phantom1975

*untested myself*

3. Re: Calculate Anniversary dates

=DATEVALUE(MONTH(B1)&"/"&DAY(B1)&"/"&YEAR(NOW())) then =DATEVALUE(MONTH(B1)&"/"&DAY(B1)&"/"&YEAR(NOW())+1)

4. Re: Calculate Anniversary dates

******** ******************** ************************************************************************>
 Microsoft Excel - Book1 ___Running: xl2002 XP : OS = Windows XP
 (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
 B2C2D2E2F2G2H2I2J2 =

A
B
C
D
E
F
G
H
I
J
2
7/23/19757/23/19767/23/19777/23/19787/23/19797/23/19807/23/19817/23/19827/23/19837/23/1984
 Sheet3

[HtmlMaker 2.32] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

5. Re: Calculate Anniversary dates

another variation, somewhat shorter, always giving this year...

******** ******************** ************************************************************************>
 Microsoft Excel - Book7 ___Running: xl2002 XP : OS = Windows XP
 (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
 B1 =

A
B
C
D
1
3/5/20033/5/2004
 Sheet1

[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

formula is:
=MONTH(A1)&"/"&DAY(A1)&"/"&YEAR(TODAY())

...as a deviation from Phantom1975's

6. Re: Calculate Anniversary dates

Thanks for the timely and great responses. My problem is solved, you guys are great. I really appreciate it.
Rupert

7. Re: Calculate Anniversary dates

my requirement was a small variation, to Zack Barresse solution. I needed to report the last completed anniversary date of an employee. I added an If condition to check this.

=IF(DATE(YEAR(TODAY()),MONTH(F4),DAY(F4))>TODAY(),DATE(YEAR(TODAY())-1,MONTH(F4),DAY(F4)),DATE(YEAR(TODAY()),MONTH(F4),DAY(F4)))

8. Re: Calculate Anniversary dates

You could also use this formula:

=EDATE(F4,DATEDIF(F4,TODAY(),"y")*12)

9. Re: Calculate Anniversary dates

Originally Posted by barry houdini
You could also use this formula:

=EDATE(F4,DATEDIF(F4,TODAY(),"y")*12)
Barry Houdini: That is really good. You gave me a huge help; however I want the Next Anniversary date so added 366 days.
=EDATE(F4,DATEDIF(F4,TODAY()+366,"y")*12)

Now it works perfectly for any start date, and gives me next years Anniversary date.
Example Start date in cell F4 was 6/1/10. Today's date is 12/3/15. It returns Next Anniversary Date as 6/1/16.
Thank you for your help! ~ MMorey

10. Re: Calculate Anniversary dates

Hi, I hope this thread is still active. I am trying to create a spreadsheet which displays milestone anniversary dates at 1 year and in multiples of 5 (1, 5, 10, 15, etc.)

I would like to use an IF statement; something like IF D5 equals 5, then display the 5-year anniversary date in E5 by adding the number of years in D5 to the the hire date displayed in C5.

If anyone could help me with this, I would greatly appreciate it. Thanks!

User Tag List

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•