+ Reply to Thread
Results 1 to 10 of 10

Vacation Entitlement based on full years of service and odd vacation year

Hybrid View

  1. #1
    Forum Expert ben_hensel's Avatar
    Join Date
    01-26-2012
    Location
    Northeast USA
    MS-Off Ver
    Office 365
    Posts
    2,043

    Re: Vacation Entitlement based on full years of service and odd vacation year

    Maybe something like:

    =IF(start_date - DATE(2017, 5, 1)>395, INDEX({10,15,20,25}, MATCH((start_date - DATE(2017, 5, 1)) /365.25, {1,4,10,18}, 1)) , MIN( INT((start_date - DATE(2017, 5, 1)) /30), 10))
    "start_date" to be changed to the appropriate cell reference for the given employee's start date.

    And that DATE(2017, 5, 1) thing might need to be changed to either a reference to another cell referencing the start date of the fiscal year (if you have one), or maybe a test that updates it to the most recent May 1st, using an IF buried in there.

    Also that's going off "30 days", not "1 month" -- it's a little different than what exactly you asked for, but fiddling with count of full months is a lot more complex.

    And you might want to feed the lookup with a table, instead of having arrays embedded in the formula. More stable that way.
    Last edited by ben_hensel; 02-01-2018 at 05:32 PM.
    Click the [* Add Reputation] Button to thank people for help!
    Post Attachment: Click [Go Advanced] under the reply box; then click [Manage Attachments] to launch the attachments dialogue.
    Mark Thread Solved: Go to the top of the first post; Select [Thread Tools] dropdown; Pick the [Mark thread as Solved] option.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Vacation Time Based on Years of Service and Position
    By MaryGemmer in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-02-2017, 04:00 AM
  2. Vacation Accrual based on fiscal year and years served
    By Jennl77 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-25-2016, 06:07 PM
  3. MTD vacation accrual formula based on yeras of service
    By lcwarren in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-03-2015, 06:03 PM
  4. Calculate Vacation Allowance based on years of service
    By Sassygirl44 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-29-2015, 03:47 PM
  5. Replies: 3
    Last Post: 10-16-2013, 09:05 PM
  6. Calculating vacation entitlement
    By kmlloyd in forum Excel General
    Replies: 2
    Last Post: 06-23-2010, 02:51 PM

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1