Holiday entitlement calculation

Hi Alastair,

Your vacation master log is excellent. It was super useful to me. Previously I was using vlookup with (today-hire date)/365 which yielded a similar results. I prefer your schedule. Thank you.

I quick questions (actually two)
1) my company policy is that people graduate to next level, say from 1-3 yrs get 10 days and 4-8 yrs get 15 days. However their anniversary is not on a hire date rather January, 1st. For e.g. someone hired on 11/15/2009 will be 3.71 yrs with the company, but as the anniversary is locked on Jan, 1st. they will get 15 days instead of 10 days.
2) if <1yrs with the company and hire date is 2/15/2013, they get 9 days, 3/15/2013 gets 8 days etc

I was wondering if you could help me lock the anniversary in Jan, 1st. to calculate YRs with the company and for <1 YR, I can probably use IF(AND((something is <1),9days etc

I appreciate your help - thank you

In the thread

http://www.excelforum.com/showthread...t=#post3346123

Green Gable committed sin #2 in attempting to hijack the thread with another question. It was very closely linked to the original post, so I am re-posting here so that I can answer without incurring the wrath of the nice, kind moderators

1) My company [holiday] policy is that people graduate to next level, say from 1-3 yrs get 10 days and 4-8 yrs get 15 days. However their anniversary is not on a hire date rather January, 1st. For e.g. someone hired on 11/15/2009 will be 3.71 yrs with the company, but as the anniversary is locked on Jan, 1st. they will get 15 days instead of 10 days.

2) if <1yrs with the company and hire date is 2/15/2013, they get 9 days, 3/15/2013 gets 8 days etc

I was wondering if you could help me lock the anniversary in Jan, 1st. to calculate YRs with the company and for <1 YR, I can probably use IF(AND((something is <1),9days etc

I appreciate your help - thank you

Regards
Alastair

Hi Green Gable

I can work out a formula, but I need to fully understand the rules. 2 extreme examples:

Employee A starts 3 Jan 2009.
a - in 2009 will he get 9 days (or does it get rounded to the nearest whole number [or rounded up, perhaps?])
b - when does he go to 15 days; 1 Jan 2013 (when he has completed 3 years 363 days) or 1 Jan 2014

Employee B starts 28 Dec 2009
a - in 2009 will he get 0 days (or does it get rounded to the nearest whole number [or rounded up, perhaps?])
b - when does he go to 15 days; 1 Jan 2013 (when he has completed 3 years 3 days) or 1 Jan 2014

Regards
Alastair

Hi Green Gable

I have attached a file which calculates the holiday allowance based on my understanding of what you asked.

Let me know if it does not do what you want.

Regards
Alastair

Hi Alastair

First of all ---- thank you Alastair

For your questions above
Employee A
a) - hire date of 3 Jan, 2009 - he will get 10 days
b) - he will go to 15 days on 1 Jan 2013
Employee B
a) - hire date of 28 Dec 2009 - he will get 0 days
b) - he will go to 15 days on 1 Jan 2013

Basically, year of hire is prorated - e.g. 1 Jan 2013 = 10 days, 1 Feb 2013 = 9 days, 1 Mar 2013 = 8 days etc.
Once in a new calendar year, you get new amount like the examples for employee A & B in questions b) above

The interesting thing is that the cut-off for the year of hire is 15th. of the month - e.g. 1 Jan 2013 will get 10 days while the person hired on 16 Jan 2013 will get 9 days.

Once again, thank you so much for your help - I really appreciate it

Hi Green Gable

Your first year explanation is very clear - as far as it goes Using your logic, people who start after 16 Oct will not get any holiday entitlement for the rest of the year. Is that really the case?

Regards
Alastair

Hi Alastair

Actually folks who start after 16 Dec 2013 will not get a holiday - for 2013 holiday schedule, our schema is like below:

-start before 15 Jan 2013 = 10 days
- before 15 Feb 2013 = 9 days
-before 15 Mar 2013 = 8 days and same for April
-before 15 May 2013 = 7 days
-before 15 June 2013 = 6 days
-before 15 July 2013 = 5 days
-before 15 Aug 2013 = 4 days
-before 15 Sep 2013 = 3 days and same for October
-before 15 Nov 2013 = 2 days
-before 15 Dec 2013 = 1 day

I really appreciate you looking into this - I am stuck with this year of hire prorate schema!

Thank you sooo much.

Regards,

Hi Green Gable

As usual daddylonglegs has come up with a very succinct answer. The only reason that I attach my solution is that his solution works for 1 cell, whereas mine works for many.

It does, however highlight an area of interpretation. I have interpreted your "start before 15 Jan 2013 = 10 days" as meaning those who start on 15 Jan 2013 do not get 10 days, whereas daddylonglegs has interpreted this as those who start "on or before" - which may well be what you intended!

Regards
Alastair

Neat!

(and I think you are right on the 15th!)

Regards
Alastair

Thank you soooo much. I will test these and I don't see any reasons why it will not work - I really appreciate your help