Vacation Days Accrued Part II

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!

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

Pete

Yes you are correct but I need ONE formula that will result in the below:

Quote:
*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.

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

Thanks!!!

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
sandymann2@mailinator.com
Replace@mailinator.com with @tiscali.co.uk
"jprogrammer" <jprogrammer.212rqm_1136319000.9632@excelforum-nospam.com>
wrote in message
news:jprogrammer.212rqm_1136319000.9632@excelforum-nospam.com...
>
> Yes you are correct but I need ONE formula that will result in the
> below: > Quote:
>> *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.
>>
>
> Exactly like my first post
> http://www.excelforum.com/showthread.php?t=494904 but the criterion is
> a little different.
>
> Thanks!!!
>
>
> --
> jprogrammer
> ------------------------------------------------------------------------
> jprogrammer's Profile:
> http://www.excelforum.com/member.php...o&userid=29781
> View this thread: http://www.excelforum.com/showthread...hreadid=497579
>

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!!! :-)

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
sandymann2@mailinator.com
Replace@mailinator.com with @tiscali.co.uk

"jprogrammer" <jprogrammer.214u9y_1136415600.8647@excelforum-nospam.com>
wrote in message
news:jprogrammer.214u9y_1136415600.8647@excelforum-nospam.com...
>
> 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!!! :-)
>
>
> --
> jprogrammer
> ------------------------------------------------------------------------
> jprogrammer's Profile:
> http://www.excelforum.com/member.php...o&userid=29781
> View this thread: http://www.excelforum.com/showthread...hreadid=497579
>