vacation accrual

Posted on 2010-01-04
Medium Priority
Last Modified: 2013-12-29
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?

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
Question by:slrichter
  • 2
LVL 10

Expert Comment

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

Expert Comment

by:barry houdini
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


Where hire date is in A1

Change 2009 to 2010 to get this year

Regards, barry

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.

LVL 50

Accepted Solution

barry houdini earned 2000 total points
ID: 26206169
OK, Shauna, you could use thsi evrsion to fix that......
regards, barry

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

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.

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question