Results 1 to 8 of 8

Calculate Anniversary dates

This is a discussion on Calculate Anniversary dates within the Excel Questions forums, part of the Question Forums category; Is there a formula I could write to calculate the anniversary dates based on a column of hire dates? For ...

  1. #1
    Board Regular
    Join Date
    Nov 2002
    Posts
    260

    Default 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.

  2. #2
    MrExcel MVP Zack Barresse's Avatar
    Join Date
    Dec 2003
    Location
    Oregon, USA
    Posts
    10,478

    Default

    how about this variation...

    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*
    Regards,
    Zack Barresse
    My book on Excel Tables
    ExcelTables.com
    All Excel Functions
    (If you would like comments in any code, please say so.)

  3. #3
    Board Regular
    Join Date
    Feb 2004
    Location
    Andover, MA
    Posts
    188

    Default Re: Calculate Anniversary dates

    =DATEVALUE(MONTH(B1)&"/"&DAY(B1)&"/"&YEAR(NOW())) then =DATEVALUE(MONTH(B1)&"/"&DAY(B1)&"/"&YEAR(NOW())+1)
    Once you can accept the universe as matter expanding into nothing that is something, wearing stripes with plaid comes easy.
    --Albert Einstein

  4. #4
    MrExcel MVP phantom1975's Avatar
    Join Date
    Jun 2002
    Location
    Omaha, Nebraska
    Posts
    3,962

    Default Re: Calculate Anniversary dates

    How about this?

    ******** ******************** ************************************************************************>
    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
    =

    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.
    Silly Billy was here....

    ***************** EXCEL/VB NEWBIES ARE MY FAVORITE! *****************

  5. #5
    MrExcel MVP Zack Barresse's Avatar
    Join Date
    Dec 2003
    Location
    Oregon, USA
    Posts
    10,478

    Default 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
    =

    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
    Regards,
    Zack Barresse
    My book on Excel Tables
    ExcelTables.com
    All Excel Functions
    (If you would like comments in any code, please say so.)

  6. #6
    Board Regular
    Join Date
    Nov 2002
    Posts
    260

    Default 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. #7
    New Member
    Join Date
    Oct 2013
    Posts
    1

    Default 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. #8
    MrExcel MVP
    Moderator
    barry houdini's Avatar
    Join Date
    Mar 2005
    Location
    England
    Posts
    19,957

    Default Re: Calculate Anniversary dates

    You could also use this formula:

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

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com