Solved

# vacation accrual

Posted on 2010-01-04
Medium Priority
423 Views
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
0
Question by:slrichter
• 2

LVL 10

Expert Comment

ID: 26178351
Would it be acceptable to use a Custom Function?
0

LVL 50

Expert Comment

ID: 26178419
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
0

Author Comment

ID: 26196969
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
0

LVL 50

Accepted Solution

barry houdini earned 2000 total points
ID: 26206169
OK, Shauna, you could use thsi evrsion to fix that......
=SUMPRODUCT(LOOKUP(DATEDIF(AC83,DATE(2009+1,{1,2,3,4,5,6,7,8,9,10,11,12},1),"m"),{-12,0,60,120,300}+12,{0,0.83,1.25,1.66,2.083}))
regards, barry
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Windows Server 2003 introduced persistent Volume Shadow Copies and made 2003 a must-do upgrade.  Since then, it's been a must-implement feature for all servers doing any kind of file sharing.
The article covers five tools all IT professionals should know about, as they up productivity by a great deal!
The Task Scheduler is a powerful tool that is built into Windows. It allows you to schedule tasks (actions) on a recurring basis, such as hourly, daily, weekly, monthly, at log on, at startup, on idle, etc. This video Micro Tutorial is a brief intro…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
###### Suggested Courses
Course of the Month14 days, 9 hours left to enroll