vacation accrual

Posted on 2010-01-04
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
    LVL 10

    Expert Comment

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

    Expert Comment

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


    Where hire date is in A1

    Change 2009 to 2010 to get this year

    Regards, barry

    Author Comment

    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

    OK, Shauna, you could use thsi evrsion to fix that......
    regards, barry

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    6 Surprising Benefits of Threat Intelligence

    All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

    Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
    NTFS file system has been developed by Microsoft that is widely used by Windows NT operating system and its advanced versions. It is the mostly used over FAT file system as it provides superior features like reliability, security, storage, efficienc…
    This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
    With the advent of Windows 10, Microsoft is pushing a Get Windows 10 icon into the notification area (system tray) of qualifying computers. There are many reasons for wanting to remove this icon. This two-part Experts Exchange video Micro Tutorial s…

    737 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

    Need Help in Real-Time?

    Connect with top rated Experts

    21 Experts available now in Live!

    Get 1:1 Help Now