Vacation Days Accrued Part II

[jprogrammer]

I posted here a few weeks ago with a very similar issue-
http://www.excelforum.com/showthread.php?t=494904

I just recently learned that we need to add a similar policy for our
office employees. This is very similar to my earlier post but they are
some differences. Here is the criterion for the office employees:

*During the first calendar year vacation will accrue at 5/12 days (3.34
hours) per complete month of service.
*Beginning January 1st following the date of hire the employee will
receive two weeks (80 hours) each year.
*Beginning January 1st following the date of hire the employee will
receive three weeks (120 hours) each year.

ANY help would very appriciated! Thanks!

 

[Pete_UK]

Having read the thread in excelforum which you refer to, I think you
third criterion relates to employees in their tenth year.

Pete

 

[jprogrammer]

Yes you are correct but I need ONE formula that will result in th
below: > Quote:

*During the first calendar year vacation will accrue at 5/12 days (3.3
hours) per complete month of service.
*Beginning January 1st following the date of hire the employee wil
receive two weeks (80 hours) each year.
*Beginning January 1st following the date of hire the employee wil
receive three weeks (120 hours) each year.

Exactly like my first pos
http://www.excelforum.com/showthread.php?t=494904 but the criterion i
a little different.

Thanks!!! :

 

[Sandy Mann]

So if someone starts of December 1st, on January 1st they will be due two
weeks holiday? Or do you mean the January 1st AFTER having completed a full
year's employment?
--
Regards

Sandy
(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk
"jprogrammer" <[email protected]>
wrote in message

 

[jprogrammer]

Sorry it took me so long to get back with you Sandy! I have so many
projects going on...

To answer your question- Yes.

So if someone starts of December 1st, on January 1st they will be due
two
weeks holiday?

I know it's messed up but I didn't write the policy. It's really not
fair to those who have been here since January 2nd 2005.

Thanks for any help!!!

 

[Sandy Mann]

jprogrammer,

With the start date in B2, this seems to do what you want:

=IF(YEAR(B2)+1=YEAR(TODAY()),10,IF(YEAR(B2)=YEAR(TODAY()),ROUND(DATEDIF(B2,TODAY(),"m")*5/12,0),IF(DATEDIF(B2,DATE(YEAR(TODAY()),1,1),"y")>=10,15,10)))

If the year number is one more than the start year number you get 10 days
holiday regardless of anything else. If we are still in the same year as
the start date it calculates the holiday entitlement using the numbers of
whole months. If the date difference between the start date and January 1st
of the current year is 10 years or more it gives you 15 days holiday and if
nothing else fits, you are entitled to 10 days.

So, in nearly all cases you get 10 days holiday within less than a year and
to get 3 weeks holiday you have to have been employed for 10 years and a
bit.

If you are an employer you may want to replace the ROUND function with
ROUNDDOWN and if you are an employee you may prefer to change it to ROUNDUP
<g> but I think that ROUND is a fair compromise.
--
HTH

Sandy
(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk

"jprogrammer" <[email protected]>
wrote in message