Calculation of annual leave

**youngjeesu** · 08-09-2012, 10:34 PM

Criteria:

Every employee is entitled to 7 days of Annual Leave which will increase by 1 day after 1 complete year of service.
(Eg, John joined the company on 1 Sep 2009. He will be entitled to 7 days of annual leave by 31 Aug 2010. From 1 Sep 2010 to 31 Aug 2011, he will be entitled to 8 days of annual leave.)

Help needed:
A formula that will help me to calculate how many days of leave does each employee is entitled to.

I have attached of the worksheet I have done so far.

A.xlsx

**icestationzbra** · 08-09-2012, 11:47 PM

does this work for you? are the calculations matching up with your expectations?

**youngjeesu** · 08-09-2012, 11:57 PM

I will try it out now! Thank you so much!!

---------- Post added at 10:57 PM ---------- Previous post was at 10:49 PM ----------

Nope, this does not work.

If i calculate it manually -

John's 1st year of service - 1 Sep 09 to 31 Aug 10 - 7 days of AL
John's 2nd year of service - 1 Sep 10 to 31 Aug 11 - 8 days of AL
John's 3rd year of service - 1 Sep 11 to 31 Aug 12 - 9 days of AL

And by the end of 2012 - 31 Aug 12 to 31 Dec 12 - 10(his entitlement)/12*4(months left in 2012)=3.34

His total entitlement by the end of the year should be 7 + 8 + 9 + 3.34.

**icestationzbra** · 08-10-2012, 01:08 AM

you should provide these illustrations either in the sample workbook or in your original / first post - slapping some numbers in a spreadsheet and sticking it up here for others to divine your expectations does not serve any purpose other than to delay.

there are two helper columns in the attached file(s), and you are better off keeping those helper columns (even though they could be merged with the main formulae) in order for the formulae to be manageable.

the following one calculates prorated AL based on months:

youngjeesu_851855_09aug12_02.xlsx

and the following one based on days:

youngjeesu_851855_09aug12_03.xlsx

does this satisfy your requirement? see which one is more correct for your purposes.