+ Reply to Thread
Results 1 to 6 of 6

Anniversary Dates

  1. 12-27-2013, 04:18 PM #1
    gjohn282
    gjohn282 is offline
    Registered User
    Join Date
    05-09-2011
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    38

    Anniversary Dates

    I need to write a formula that will precisely tell me how many years an employee has been with the company using Today() and a Hire Date. I will need to use the Floor fuction so that the result will only increase in value only when a new whole number rolls over. The tricky part is of course taking leap year into consideration. I've playred around with the Edate function but I'm still having trouble putting it all together.

    The anniversary date for the employee will increase when the month & day for Today() is equal to or greather than the hire date. Thanks!

    Example:
    Today Hire Date Yrs with Company
    Dec 27th, 2013 Dec 27th, 2012 1
    Dec 27th, 2013 Dec 26th, 2012 0
    Dec 27th, 2013 Dec 27th, 2011 2
    Dec 27th, 2013 Dec 26th, 2011 1
    Dec 27th, 2013 Dec 27th, 2010 3
    Dec 27th, 2013 Dec 26th, 2010 2
    Dec 27th, 2013 Dec 27th, 2009 4
    Dec 27th, 2013 Dec 26th, 2009 3
    Last edited by gjohn282; 12-27-2013 at 06:12 PM.
    Reply With QuoteRegister To Reply
  2. 12-27-2013, 04:21 PM #2
    Pete_UK
    Pete_UK is offline
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,704

    Re: Anniversary Dates

    Hasn't someone who started working on 26th Dec 2012 been working for you longer than someone who started on 27th Dec 2012? I think you need to revise the information in your table.

    Hope this helps.

    Pete
    Reply With QuoteRegister To Reply
  3. 12-27-2013, 04:23 PM #3
    Pepe Le Mokko
    Pepe Le Mokko is offline
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,446

    Re: Anniversary Dates

    try = DATEDIF(Hire_date,today(),"y")
    How to paste Power Query code ( by Olly)
    Reply With QuoteRegister To Reply
  4. 12-27-2013, 04:24 PM #4
    daddylonglegs
    daddylonglegs is offline
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: Anniversary Dates

    Try using DATEDIF function

    if you have hire date in B2 this formula will give you the number of full years elapsed and will only increment on the anniversary date

    =DATEDIF(B2,TODAY(),"y")
    Audere est facere
    Reply With QuoteRegister To Reply
  5. 12-27-2013, 05:34 PM #5
    gjohn282
    gjohn282 is offline
    Registered User
    Join Date
    05-09-2011
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    38

    Re: Anniversary Dates

    You are right Pete_UK, thanks for catching. Must be a Firday! Yes, my example should have looked like this below. Thank you both (Pepe Le Mokko and daddylonglegs) that was exactly what I'm looking for. I have never even heard of that formula and have been using Excel for years. I guess you learn something new every day! Wish you all a Happy New Year. Thanks for the speedy reply.


    Today Hire Date Yrs with Company
    Dec 27th, 2013 Dec 27th, 2012 1
    Dec 27th, 2013 Dec 28th, 2012 0
    Dec 27th, 2013 Dec 27th, 2011 2
    Dec 27th, 2013 Dec 28th, 2011 1
    Dec 27th, 2013 Dec 27th, 2010 3
    Dec 27th, 2013 Dec 28th, 2010 2
    Dec 27th, 2013 Dec 27th, 2009 4
    Dec 27th, 2013 Dec 28th, 2009 3
    Reply With QuoteRegister To Reply
  6. 12-27-2013, 05:42 PM #6
    Pete_UK
    Pete_UK is offline
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,704

    Re: Anniversary Dates

    The DATEDIF function has been around since at least XL2000, where it was documented in the Help files, but although it has been included in all Excel versions since then, it has not appeared in any of the help files. For more information about it you can look on Chip Pearson's site, here:

    http://www.cpearson.com/excel/datedif.aspx

    Hope this helps.

    Pete
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Identifying the annual anniversary dates
    By y_not in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 12-03-2013, 03:25 AM
  2. Anniversary dates
    By AnotherDumbAss in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-23-2013, 07:06 PM
  3. Formula to recognize anniversary dates
    By Tanijan in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 10-04-2012, 11:08 AM
  4. My 1st Anniversary
    By Blake 7 in forum The Water Cooler
    Replies: 97
    Last Post: 10-05-2011, 05:11 AM
  5. [SOLVED] recurring anniversary dates
    By Blackhawk in forum Excel General
    Replies: 0
    Last Post: 08-02-2005, 02:05 AM

Tags for this Thread

Bookmarks

Posting Permissions

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1