Results 1 to 3 of 3

Thread: Calculating Vacation Time at Beginning of Year

  1. #1
    Seeker ctiger's Avatar
    Join Date
    Nov 2013
    Posts
    16
    Articles
    0
    Excel Version
    2013

    Calculating Vacation Time at Beginning of Year



    Register for a FREE account, and/
    or Log in to avoid these ads!

    I have a formula that will calculate the vacation time earned based on hire date. The amount of time changes based on years of service.
    Hire Date Vacation Time
    1/1/1994 80
    8/20/2013 0
    8/20/2008 40

    The formula works great - =LOOKUP(DATEDIF($C3,TODAY(),"Y"),{0,1,5,9,10},{0,80,40,40,80}). My problem now is that the policy changed and the time earned BEGINS at the start of the new year, so for example the person hired on 8/20/2008 would not earn their time until 1/1/2009. How can I add that part to my current formula? Thanks Gene

  2. #2
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,489
    Articles
    0
    Excel Version
    Excel 2016
    Assuming that for the first Hire date, you want to count from that date since it is on the first, then

    =IFERROR(LOOKUP(DATEDIF(DATE(YEAR($C3)+(TEXT($C3,"d/m")<>"1/1"),1,1),TODAY(),"Y"),{0,1,5,9,10},{0,80,40,40,80}),0)

    if you still want to go to next year..

    =IFERROR(LOOKUP(DATEDIF(DATE(YEAR($C3)+1,1,1),TODAY(),"Y"),{0,1,5,9,10},{0,80,40,40,80}),0)


  3. #3
    Seeker ctiger's Avatar
    Join Date
    Nov 2013
    Posts
    16
    Articles
    0
    Excel Version
    2013
    Works great, using the second formula, thanks so much for the help!

Posting Permissions

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