Vacation Accrual

**kanolan** · 04-23-2009, 01:36 PM

Hi everyone,
Thanks to all of you, I’ve made great progress in my vacation accrual spreadsheet, but have some new problems I hope you can help me out with because I’m at a total loss as to how to even start. I’ve attached my spreadsheet so you can take a look and hopefully give me some ideas.

The first problem is that employees change their accrual rate based on their hire date, but vacation is accrued on a calendar year (Jan-Dec). For example, emp. # 6, Matthew Doe’s hire date was 3/4/08. Based on that, he would accrue vacation at the 1 week rate (0.769) from Jan 1, 2009 through 3/3/09 and would accrue at the 2 week rate (1.539) from 3/4/09 through 12/31/09. How would I write formulas in the hours earned and # of hours columns for these situations?

Along that same line, how would I write the formulas for new hires like emp. #18, Betty Doe who should only start accruing vacation as of her start date, but it calculates her as accruing since January 1?

Also, we have several part-time folks who work anywhere from 4 to 6.5 hours per day 2, 3 or 4 days per week (that also may or may not change their accrual rate through the year). They would earn the same # of weeks based upon the hours they work – say emp. #17, Greg Doe only works 3 days per week, 5 hours per day. He would be accruing 2 weeks of vacation time, which would be 15 hours per week for a total of 30 hours, but he’s also changing to the 3 week accrual schedule as of 5/10/09.

I’m not very well versed in formula writing and the last two columns of the spreadsheet calculate time used and time remaining. The formulas I wrote are very basic (a2+c2+e2 etc) and I was wondering if there is a better way to do this that would be more accurate?

Sorry for the long post, but I really wanted to make sure that I explained this as best I could. I’m at a complete loss as to how to even start this and any help at all would be much appreciated! Thanks everyone!

**mrice** · 04-23-2009, 03:56 PM

I believe that you can solve this one with a user defined function.

Function AccrualThisYear(HireDate As Range) As Single
Dim AnniversaryDate As Date
Dim ServiceAtAnniverary As Integer
Dim BeforeAnniverary As Single
Dim AfterAnniverary As Single

AnniversaryDate = DateValue(Left(HireDate, 6) & Year(Now()))
ServiceAtAnniverary = Round((AnniversaryDate - HireDate) / 365.5)
If AnniversaryDate < Date Then
    Select Case ServiceAtAnniverary - 1
        Case Is < 1
            AccrualRate = 0.769
        Case Is < 5
            AccrualRate = 1.538
        Case Is >= 5
            AccrualRate = 2.307
    End Select
    BeforeAnniverary = ((AnniversaryDate - DateValue("01/01/" & Year(Now()))) / 7) * AccrualRate
    
    Select Case ServiceAtAnniverary
        Case Is < 1
            AccrualRate = 0.769
        Case Is < 5
            AccrualRate = 1.538
        Case Is >= 5
            AccrualRate = 2.307
    End Select
    AfterAnniverary = ((Date - AnniversaryDate) / 7) * AccrualRate
    
    AccrualThisYear = BeforeAnniverary + AfterAnniverary
Else
    Select Case ServiceAtAnniverary - 1
        Case Is < 1
            AccrualRate = 0.769
        Case Is < 5
            AccrualRate = 1.538
        Case Is >= 5
            AccrualRate = 2.307
    End Select
    AccrualThisYear = ((Date - DateValue("01/01/" & Year(Now()))) / 7) * AccrualRate
    
End If

End Function

To use this, open a new module in the VBA editor (Alt F11) and paste in the code.

In the spreadsheet enter a formula such as

=AccrualThisYear(D4)

Hope this works and that the members of the Doe family enjoy their holidays.

**kanolan** · 04-23-2009, 04:32 PM

Thanks so much for your help, Martin (the Doe family thanks you also!) but I'm a little confused as I've never used VBA editor before and am not quite sure what to do with it? I hit alt f11, pasted the code, and entered the formula, but I get a name error? I'm not that fluent with excel and apologize for my inexperience - I'm sure I'm doing something wrong lol!