Annual Leave Calculation Problem

Since my company is going to provide 3 more paid holidays as incentives for those who has worked with us for more than 5 years, I would like to ask how could I set formulas in an excel document to find out the following items:

Criteria
(1) normally, each employee is entitled 11 days of annual leave per year

(2) to simplify, our company divides annual leave days by months then calculate the exact days by dividing the days one worked in the month with the total days in the month (i.e. if one started to work from on 20th Oct, 2005, then in Oct of that year he gets 11/12 days of annual leaves times 11/30 days in Oct = 0.336 days of annual leave)

(3) once an employee has worked 5 years with us, he or she will be given 3 days of extra annual leave (i.e. from 20th Oct, 2010 onwards, he/she gets 14 days of annual leave per year or 1.167 days per month)

(4) the only information we have is the date when individual employees started to work in the company

Problems are:
(1) how could we accurately find out the total number of annual leave days one can get in a year up to today

(2) in some situations, one may achieve 5 years of work during the year instead of at the beginning of a year (i.e. if he/she has worked for 5 years on 20th Oct this year, he gets 0.917 days for each month (as well as the 20days from 1st to 20th) before 20th Oct , and 1.167 days per month since 21st Oct)


Please suggest how can I create formula(s) to calculate the above.

Thousands of THANKSSSSSSSSSSS!!!!!!!

The following is based on leave accrual per day, not month. Could be done with month but would be more complicated.


B2= Employee Start Date
B3= As at Date or Today()

=IF(B3-B2<=(365*5),((B3-B2)*(11/365)),((365*5)*(11/365))+(B3-(B2+(365*5)))*(14/365))

11 & 14 could be replaced with cell references for standard and 5+ entitlement.

Hope this Helps

Originally Posted by pforparis

Since my company is going to provide 3 more paid holidays as incentives for those who has worked with us for more than 5 years, I would like to ask how could I set formulas in an excel document to find out the following items:

Criteria
(1) normally, each employee is entitled 11 days of annual leave per year

(2) to simplify, our company divides annual leave days by months then calculate the exact days by dividing the days one worked in the month with the total days in the month (i.e. if one started to work from on 20th Oct, 2005, then in Oct of that year he gets 11/12 days of annual leaves times 11/30 days in Oct = 0.336 days of annual leave)

(3) once an employee has worked 5 years with us, he or she will be given 3 days of extra annual leave (i.e. from 20th Oct, 2010 onwards, he/she gets 14 days of annual leave per year or 1.167 days per month)

(4) the only information we have is the date when individual employees started to work in the company

Problems are:
(1) how could we accurately find out the total number of annual leave days one can get in a year up to today

(2) in some situations, one may achieve 5 years of work during the year instead of at the beginning of a year (i.e. if he/she has worked for 5 years on 20th Oct this year, he gets 0.917 days for each month (as well as the 20days from 1st to 20th) before 20th Oct , and 1.167 days per month since 21st Oct)


Please suggest how can I create formula(s) to calculate the above.

Thousands of THANKSSSSSSSSSSS!!!!!!!