+ Reply to Thread
Results 1 to 7 of 7

PTO Accrual formula

  1. #1
    Registered User
    Join Date
    10-28-2014
    Location
    san antonio
    MS-Off Ver
    2013
    Posts
    4

    PTO Accrual formula


    I am learning HR, and I was given the task of coming up with an employee database spreadsheet. I figured out how to do the formula for insurance eligibility, years worked and vacation days, but I am having the hardest time coming up with a formula for paid time off accrual. We accrue it at .42 days/month, making it 5 days per year. It accrues beginning with the hire date, and if you don't use it within the year, you lose it. I tried using the networkdays function and the edate function. I'm just not sure where to begin. Any gurus out there that can help?
    Attached Files Attached Files

  2. #2
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,578

    Re: PTO Accrual formula

    When does it start accruing? If they are hired on May 2, or May 15 or May 28th, when do they get their first 0.42 days?
    if you don't use it within the year, you lose it
    Is that by the end of the year or a year measured from hire date, i.e. May 2 to May 2?
    Last edited by ChemistB; 11-05-2014 at 01:24 PM.
    ChemistB
    My 2?

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  3. #3
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,578

    Re: PTO Accrual formula

    For Column S "Years Worked" you have
    =YEAR([@Today])-YEAR([@[Hire Date]])
    So if they were hired Dec 2014, in Jan 2015, they are considered having worked 1 year?

  4. #4
    Registered User
    Join Date
    10-28-2014
    Location
    san antonio
    MS-Off Ver
    2013
    Posts
    4

    Re: PTO Accrual formula

    Sorry, I had to leave for an appointment. So, they start accruing their time at date of hire, but cant use it until their 90 day probationary period is up. We did this so that it coincides with their vacation time, which starts at their hire date.

  5. #5
    Registered User
    Join Date
    10-28-2014
    Location
    san antonio
    MS-Off Ver
    2013
    Posts
    4

    Re: PTO Accrual formula

    No, if they were hired Dec 2014, their year would not be complete until Dec 2015.

  6. #6
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,578

    Re: PTO Accrual formula

    Okay, so calculated it this way.
    All time is calculated from hire date. So if Hire date is May 2, 2014, June 2 is 1 month, May 2, 2015 is 1 year.
    For first 2 years, vacation accrues at 0.42 days/month. At 2 years, it starts at 0.84, then 1.26, 1.68 etc.
    So on May 2, 2015, all vacation days accrued are lost and begin counting again at 0.42/month.
    On May 2, 2016, same thing except start accruing at 0.84 per month.

    If that is all true, then S8 (Years Worked)

    = IF(ISNUMBER([@[Hire Date]]),YEAR(TODAY())-YEAR([@[Hire Date]]) - IF(DATE(YEAR(TODAY()), MONTH([@[Hire Date]]), DAY([@[Hire Date]]))<TODAY(), 0,1),"")

    T8 (Vac Days accrued)

    =IF(ISNUMBER([@[Hire Date]]),(IF(DATE(YEAR(TODAY()),MONTH([@[Hire Date]]),DAY([@[Hire Date]]))< DATE(YEAR(TODAY()),MONTH(TODAY()),DAY(TODAY())), 0,12) +MONTH(TODAY())-MONTH([@[Hire Date]])-IF(DAY(TODAY())>= DAY([@[Hire Date]]),0,1))* IF([@[Yrs Worked]]<2, 0.42, [@[Yrs Worked]]*0.42),"")
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    10-28-2014
    Location
    san antonio
    MS-Off Ver
    2013
    Posts
    4

    Re: PTO Accrual formula

    Well, almost. I think I can change it up. Vacation days are just given to us-- 5 days after a year from date of hire and those can roll over. The paid time off is accrued at .42/mo but do not roll over. I think I can work with what you did. I so appreciate you!! I am at a job where I am using excel more frequently than at a previous job, I may become a regular here! Thank you so much ChemistB

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Need Formula for Vacation Accrual
    By indygoof2007 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 10-29-2014, 11:45 PM
  2. Formula for vacation accrual
    By zandiago1 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 08-20-2013, 09:48 PM
  3. Vacation Accrual Formula
    By Cullen8 in forum Excel General
    Replies: 3
    Last Post: 03-22-2012, 04:31 PM
  4. Vacation Accrual Formula
    By MissNadine in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 12-18-2009, 02:48 PM
  5. Please help!! Vacation Accrual Formula
    By MissNadine in forum Excel Formulas & Functions
    Replies: 20
    Last Post: 09-06-2005, 07:05 PM

Tags for this Thread

Bookmarks

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