Closed Thread
Results 1 to 5 of 5

Vacation Accrual Formula

  1. #1
    Registered User
    Join Date

    UPDATE: Vacation Accrual Formula FOUND!!!

    Ok, I had to ask my old boss for the spreadsheet and he sent it to me and it's an amazing formula! If anyone needs a spreadsheet that accrues vacation days automatically based on the current date, let me know and I'll send it to you. I'm still confused about one of the formulas and the value that it creates but least it works!


    OK, I actually created this formula several years ago at my old job but since I am no longer there, I have no access to it. My boss helped me with it and I just can't remember the formula.

    Basically, I need to create a spreadsheet showing number of accumulated vacation days for an employee based on the number of days they accrue per day per year.
    Here is the pertinent information - all I remember from the last time I had to deal with this formula.
    We created a formula that basically used the current date (of the day you actually accessed the spreadsheet) to calculate the number of days (with like 2 decimal places) the employee had accrued to date.

    This is the info we used to generate the number:

    Anniversary date
    Days accrued for year 1, 2, 3, 4, 5+
    Year one - 5 days
    Year two - 5 days
    Year three - 10 days
    Year four - 10 days
    Year five and each year thereafter - 15 days

    Accrual rate for each year:

    Year one: .01369 days per day
    Year two: .01369 days per day
    Year three: .02739 days per day
    Year four: .02739 days per day
    Year five and on: .04109 days per day

    Also taking in consideration that any days not used the previous year, roll over to the next year.

    For example, employee one has an anniversary date of 7/1/2003. As of 7/1/2005 there are no days accrued because they all have been used so I'm starting from zero. To date, there have been 48 days since her anniversary date, therefore, she has accrued 1.31 days to date. It takes approximately 36.5 days to accrue 1 vacation day.

    can someone please help me with this?

    Last edited by MissNadine; 08-18-2005 at 07:17 PM. Reason: I have the solution - finally!

  2. #2
    Registered User
    Join Date

    Vacation Accrual Spreadsheet needed

    I am extremely interested in the vacation accrual spreadsheet you designed. I'm currently in the process of creating one for our company and have no idea on how to get started. If there is a possibility you can share it i would greatly appreciate it.

  3. #3
    Forum Expert darkyam's Avatar
    Join Date
    Houston, TX
    MS-Off Ver
    10 days per year gives an accrual rate of 0.037736. The basic formula would be =(TODAY()-Date(Year(Today()),Month(AnniversaryDate),Day(AnniversaryDate))+1)*VLOOKUP(Year(Today()-AnniversaryDate)-1899,LookupTableOfDaysAccruedPerYear,2,True)+rollover. If you can zip and post your workbook, I can give exact cell references.

  4. #4
    Registered User
    Join Date

    Vacation Accrual Spreadsheet

    I would LOVE a copy of this. Please send - I need one desperately!!!!

  5. #5
    Registered User
    Join Date
    MS-Off Ver
    Excel 2003

    Re: Vacation Accrual Formula

    I would also like a copy a of this. Would you please send me one? Thanks.

Closed Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1