asked on

vacation accrual

I currently use the below formula to calculate an employees years of service:
=IF(ISERR(DATEDIF(AB71,AB72,"y") & " years, " & DATEDIF(AB71,AB72,"ym") & " months," & DATEDIF(AB71,AB72,"md") & " days"),0,(DATEDIF(AB71,AB72,"y") & " years, " & DATEDIF(AB71,AB72,"ym") & " months," & DATEDIF(AB71,AB72,"md") & " days"))
3 years, 5 months,14 days
and have the same rate of accrual as "marcisms" question previously posted, please see below

In these formulas how do you account for a change of rate of accrual in midyear?

Example:
I start work for a company on 8/31/2004 and I am earning .83 days per month of vacation. According to my company on 9/1/2009 I will have completed 5 years of service and will begin accruing 1.25 days of vacation per month.

So what is the formula for figuring out how many vacation days I will earn this year. I know I will earn 8 months of .83 days and 4 months of 1.25 days = 11.64 but I can't figure out how to write the formula to do this.

0 - 60 months earn .83/month
61-120 months earn 1.25/month
121 + months earn 1.66/month

bromy2004

Would it be acceptable to use a Custom Function?

barry houdini

If you want to calculate amount accrued for a calendar year, with vacation accrued on the 1st of each month then try this formula for 2009

=SUMPRODUCT(LOOKUP(DATEDIF(A1,DATE(2009,{1,2,3,4,5,6,7,8,9,10,11,12},1),"m"),{0,60,120;0.83,1.25,1.66}))

Where hire date is in A1

Change 2009 to 2010 to get this year

Regards, barry

slrichter

ASKER

Hi Barry,

Used your formula but it seems that when I input a hire date for example March 03, 2009 an error message populates - it seems to work fine with any date more than a year. Could I please have you assist me?

Thank you.

Shauna
timecard-eg-with-forumal-for-day.xlsx

ASKER CERTIFIED SOLUTION

barry houdini

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial