+ Reply to Thread
Results 1 to 5 of 5

Calculate Leave Accural In a year using staff member tennur

  1. #1
    Forum Contributor
    Join Date
    12-24-2010
    Location
    South Africa
    MS-Off Ver
    Office 2003; Office 2007; Office 2010 (Prefered)
    Posts
    102

    Calculate Leave Accural In a year using staff member tennur

    Hi All,

    I am wanting to calculate the leave acquired by an employee on a month by month basis. The formula I am currently using however does cater for change in the accural calculator.

    Example: Employees Acquire Leave as follows:
    1 Year = 1.25 days per month
    2 Years = 1.33 days per month
    3 Years = 1.42 days per month
    4 Years = 1.5 days per month
    5 Years = 1.58 days per month
    >=6 Years = 1.67 days per month

    The calculation error happens when an employee crosses over to a new accural band as per the example:

    Using a Start Date of 01/11/2012

    From January to November of 2014 they will acquire leave at 1.33 days per annum, however when it gets to December their leave accural will increase to 1.42. I need to calculate their acquired leave at a rate of 1.33 for the first 11 months, and then at the new accural rate for the last month only. The accumulated leave should equal 16.05 and not 17.04 after the 12 month period.

    Sample attached

    Thanks
    Attached Files Attached Files
    Last edited by SalientAnimal; 01-02-2014 at 06:48 AM.

  2. #2
    Forum Contributor
    Join Date
    03-17-2005
    Location
    Canterbury, Kent
    MS-Off Ver
    2003, 2010
    Posts
    285

    Re: Calculate Leave Accural In a year using staff member tennur

    Where on the attached sheet do you record the staff members start date?

  3. #3
    Forum Contributor
    Join Date
    12-24-2010
    Location
    South Africa
    MS-Off Ver
    Office 2003; Office 2007; Office 2010 (Prefered)
    Posts
    102

    Re: Calculate Leave Accural In a year using staff member tennur

    Quote Originally Posted by windy58 View Post
    Where on the attached sheet do you record the staff members start date?
    There is a table at the bottom that calculates all the values / totals as well as the start date.

  4. #4
    Forum Contributor
    Join Date
    12-24-2010
    Location
    South Africa
    MS-Off Ver
    Office 2003; Office 2007; Office 2010 (Prefered)
    Posts
    102

    Re: Calculate Leave Accural In a year using staff member tennur

    Any help with this yet guys? Really want to try get a solution.

    Thanks.

  5. #5
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,513

    Re: Calculate Leave Accural In a year using staff member tennur

    In J145 use

    =INDEX({1.25,1.33,1.42,1.5,1.58,1.67},MATCH($H145,{0,12,24,36,48,60,999999},1))

+ 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. Replies: 2
    Last Post: 09-15-2013, 03:52 PM
  2. VBA to automatically populate Excel calendar with staff vacation (leave) data
    By batteredveg in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 03-21-2013, 04:09 PM
  3. staff leave and sick leave planner
    By Just granite in forum Excel General
    Replies: 1
    Last Post: 05-25-2012, 03:15 AM
  4. Which staff member has the most sales in this month?
    By shazzm in forum Excel General
    Replies: 2
    Last Post: 04-28-2011, 03:46 AM
  5. annual leave planner for approx 100 staff members
    By wally in forum Excel General
    Replies: 1
    Last Post: 03-22-2005, 08:06 AM

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