PTO Accrual formula

1. 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?

2. 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?

3. 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. 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. Re: PTO Accrual formula

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

6. 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),"")

7. 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

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