Calculating Employee Leave Accrual

I need some help with leave calculations. The start date is the date they started with the company and this is the base for everything (it is in column "B")

Second criteria is how long they have been with the company I used =(YEAR(NOW())-YEAR(Bxx))*12+MONTH(NOW())-MONTH(Bxx) to determine the months in column H

The employee doesn't earn any leave until after 6 months. On the 6th month anniversary they automatically receive 12 days (96hrs) and an additional 2 days (16 Hrs) each month thereafter. Now if that were the end it would be simple with =IF(Hxx<6,0,Hxx*2) in column I. However, once at the employee reaches 12 months they are awarded 30 days with the maximum accumulation being 60 days.

So if written out it would look like this:
1-5 months = 0 days of leave
6-12 months = 12 days plus 2 days per month (total for the first year 24 days)
12 months and more = 30 days a year not to exceed 60 days.
Note: The leave days do not accumulate when advancing in time. Example: if the employee uses 6 days during his 10th month with the company when he gets to one year he will have 24 days available.

Once I get this, any leave taken can simply be subtracted from this total.

Any questions let me know, thanks in advance for any assistance.

Kyle

Hi -

I think the simplest would be to use a VLOOKUP with a table for the first 12 months rather than a complex formula. It's a lot easier to debug a table than a formula. After 12 months I assumed 30 days was awarded at the end of each completed year (that may or may not be correct, you didn't specify). So I wrote the following formula:

=MIN(IF(H2<=12,VLOOKUP(H2,$N$2:$O$10,2),(INT(H2/12)-1)*30+24),60)

First, it takes the minimum value of either the computation or 60. So if the computed leave goes over 60, it just uses 60.

Second, it uses VLOOKUP of the employees tenure is less than or equal to 12 months. So 0 to 5 is 0, 6 is 12, 7 is 14, etc. You can see the table I set up in the attached spreadsheet.

Finally, if it's over a year, it takes the tenure, divided by 12 and rounded down to an even integer and multiplies by 30 days and adds 24 for the first year. So, for example, If your tenure is 24 months, that is 2 years. So one year at 30 days plus the 24 days for the first year for a total of 54 days. If you are there 40 months, that is 3.333 years, which is rounded down to 3. 2 years at 30 = 60 plus the original 24 for the first year is 84 BUT, that is greater than 60, so the formula shows 60.

You can see this in the attached spreadsheet. Not sure I fully understood all your directions. Hope this helps.

I like the idea of a table and this does work for the application. Only thing I see, and I probably didn't explain it well, at 12 months the leave earned from 6-12 months (24 Days) does not get added to the 30 days they earn once they hit 12 months.

Explanation:
tenure is 10 months I have 20 days available
tenure is 12 months, I have 24 days available
Tenure is 13 months, I have 30 days available (not 54)
This is assuming that I have not taken any time off since being hired...

Hi -

OK, attached is a revised version of that spreadsheet that adds 13 months and 30 days to the table. For accumulated leave beyond 13 months, it takes the integer value of years of service times 30. So for 2 years, it's 60 days. 3 years it's still 60 days because of the maximum. The formula looks like this:

=MIN(IF(H2<=13,VLOOKUP(H2,$N$2:$O$10,2),(INT(H2/12))*30),60)

See the attached spreadsheet.

Hope this helps.