Free Microsoft Excel 2013 Quick Reference

Calculate PTO ACCRUAL

Hi,

I volunteer for a small non-profit, and I have been asked to help with a spreadsheet for tracking PTO accrual. I am not very experienced, and I need some guidance. I have attached a sample spreadsheet with the PTO accrual rates and dates of Hire. Any help we would be very grateful.

Thanks!

PTO accrues from 1/1-12/31.


Post your answer or comment

comments powered by Disqus
Hello,

I am working on a PTO accrual spreadsheet. Although I have been teaching myself everything from this site and
books, I'm having difficulty constructing this last formula. I have attached a sample so you can see what I'm talking about.

In A27, I am calculating total PTO for the year. In this case, the employee was hired 11/14/11, and accrues 1 day monthly from 1/1/12 to 11/14/12. On 11/15/12, she begins to accrue 1.25 days until 1/1/13. The current formula only calculates PTO from 1/1/12-11/14/12. I just can't figure out how to take this number, and then add it to what accrues at the different rate from 11/14/12-1/1/13.

If anyone has thoughts it would be greatly appreciated.

Thanks

I am attempting to calculate vacation accrual so that I can enter into
QuickBooks.

Vacation accrues as follows:
1) employee gets 1 week (40 hrs) after 6 month employment
2) employee receives 1 week vacation for every 6 months worked or portion
thereof
3) maximum vacation an employee can accrue is 4 weeks (160 hrs)
4) employee is paid bi-weekly (26 pay periods/2080 hrs annually)

I am attempting to create a formula to calculate the bi-weekly accrual rate
based on the above information.

Thank you

PTO Calculation.xlsI am making a spreadsheet that calculates PTO for employees. Employees with 0-5 yrs get 2.308 hours a week up to 120 hrs/year. Employees with 5+ yrs get 3.077 hours a week up to 160 hrs/year. I am attaching the setup that I would like to use. I came up with a formula that works except for 1 problem:
If the employee reached their 5 yr mark during the year,such as employee #2 does in the attachment, how can I formulate 2.308hrs/week up until they reach their 5yr mark and then do 3.077hrs/week from then on out?

Hi,

I have been working on the attached for some time. I posted yesterday, but I was able to get a lot further.
The only thing I am still fighting are columns PTO Monthly and Daily. I am trying to show what employees accrue. I have attached the file, and would be grateful for any input. Thanks

Great Forum!

I searched, but was unable to find something specfically for what I am looking for, which leads me to believe it may not be possible or I am not wording my search appropriately, which negates results.

My employer has come come up with some weird calculation to determine if employees get paid out on accumulated vacation leave or not. No one can figure it out because it is something you only do once a year so I was going to try to do an excel calculation to make it ez.

The first step was to put in today's date which I figured out to be NOW().The employee would put in his/her date of hireA cell would automatically figure out the employees years of service. (Year/Month/Day)Based on the years of service, a monthly accrual figure would populate in the next cell, which could be 6 different values.Additional calculations will be done, which include math and multiplying and I will be able to handle those.My first problem is step 3:
When I put a date in the date of hire I get the Year, month and the day, but the day is in a decimal format. I cannot figure out why.

My Second problem is step 4:
I cannot figure out how to make it so Excel recognizes the years of service as a value. And when it does how to make that value a If and Then fuction with 6 values. Here are the functions I am trying to accomplish depending on where the years of service fall. Book1.xlsx

1.5 years to < 5years = 96
>5 Years to 10 Years to 15 Years to 20 Years to 25 Years = 200

I attached what I have so far. Thank you.

I am trying to calculate annual leave time for employees. Time is accrued monthly. I am having trouble calculating leave time accrued in years when employees hit major milestones - such as five years or ten years. We accrue time based on the following schedule:

Full Year's Service Monthly Accrual Rate
Up to 5 years 6 hours
5-10 years 8 hours
Over 10 years 10 hours

In the major milestone years, once the anniversary date hits, the annual leave is accruing at the new rate rather than prorating for previous time earned.

Example: Say an employee has a start date of 6/17/2002. Time accrues correctly year to date (we create a new leave sheet each year, so time is tracked for the year beginning January 1) up until 6/17/12. Until then, the employee gets 8 hours a month. Once 6/17/12 comes, beginning on July 1st(we earn the time, then accrue it), the employee should get 10 hours a month. At the end of the year, this employee should get 108 hours of leave (January-June - 6 hours/ month and July-December - 10 hours/ month).

In trying to figure this out, I found a post and copied a formula that seems very close:
=(VLOOKUP((C3-B38)/365.25,{0,6;4.999,8;9.999,10;999,10},2,TRUE)*IF(MOD((C3-B38)/365.25,5)

I hope I posted this in the right place:

I want to make a excel spreadsheet that will use information from one chart that I have already created. What I'm trying to do is make a PTO calculator that will generate accrued PTO days based off someones start date, position, and years of service. I need a formula that will take the chart information and adjust peoples PTOs according to the chart and corporate policies.

So for example if Joe started on January 1st, 2005, he is a full-time worker, and has worked from January 1, 2005 - Present day then he would have worked at the company for approximately 5+ years. Now according to the chart that I have, a full-time worker depending on their amount of years worked would receive a certain amount of PTO. Additionally, if the person was in a higher position they would receive another PTO accrued.

I have an idea of what I want the chart to do. Basically it'll will have the following columns: Last name, First name, Hire Date, Position, Years of Service, Allowed PTO, Accrued PTO. Some columns will have information imputted in mannually. For example Last name, First name, Hire Date, and Position. But the years of service column should automatically calculate the number of years the person has worked using the hire date. The allowed PTO column should take the information from years of service and position to find from the premade chart what PTO allowance the employee has. Finally the Accrued PTO will reflect how much PTO they person has accrued. Additionally, our corporate policy is that no one can carry over more then 5 PTO days and those 5 PTO days will expire in June of the following year.

Does any body have a solution for this? Any suggestions are welcome!

I am trying to set up a spreadsheet to track PTO. I have attached my spreadsheet. first tab is the accrual rate and the second tab is accumulated time off. I have a second spreadsheet that i will connect as a pivot table to give me actual. I am looking for a formula to help me with the accumulated based on accrual rate. Any help would be greatly appreciated!

I have a 2010 Attendance Sheet in Excel (sample attached). It's organized into five sheets: a year-to-date summary sheet, plus one sheet for each quarter. It tracks vacation, doctor's appointments, workshops, sick days, etc. On the Summary sheet there is a formula that calculates how many vacation days and sick days are left.

Sick days accrue at a rate of .5 day per month, unless a sick day was taken in that month. So, if you take a sick day in January, you don't earn your additional .5 day.

In column R on the Summary sheet, I have a formula to calculate the accrual of sick days. In column U, I'm trying to develop a formula that will look at each month, for each employee and calculate whether or not .5 day should accrue for that month. I have gotten myself totally confused. Before I tried to allow for months when the .5 day would not accrue, I just had to caculate the number of months elapsed to date and divide by two.

I then created EmployeeMonth named cells, e.g., JanuarySmith, FebruarySmith and so on, to calculate IF JanuarySmith has an "i" no .5 day will accrue for employee Smith. Now I am stuck . . .

Any suggestions would be much appreciated!

When our employees reach 5 years and 15 years, their vacation accrual is prorated based on their hire date. I'm trying to come up with a formula that will calculate this for me.

Example: Employee hired May 10, 2005. Will accrue 3.33 hours for each pay period (24) until the pay period following his anniversary date at which time he will begin to accrue 5 hours per pay period.

So, for this year, this employee will accrue 3.33 hours * 10 pay periods (1/1 thru 5/15) =33.3 hours. Beginning at the end of May, he will accrue 5 hours per pay period * 14 remaining pay periods = 70 hours for a total of 103.3 hours.

Can anyone provide a solution?

Thanks in advance!

Hello all,

I have no experience in Excel VBA before, that's why I tried hard to use a mixture of Excel existing formulas to calculate the accrual sum of revenue for a school's course (with a duration of less than 12 months, and 6 batches of students), and I failed several times to do so

I've heard that we can use Loop in Excel VBA to execute the task easier and more efficiently. I kept searching but my limited knowledge of coding prevented me from success.

Would you please have a look at the attached example, and teach me how to generate a Macro to implement the task. Thanks a lot for your help.

Could I please ask for assitance with a formula that would calculate the number of days accured (AD71) based on a person start date (AC67), please see attached template.

We earn at a rate of:

.8333 (less than 5 years)
1.25 (5 years but less than 10 years)
1.667 (10 years but less than 20 years
2.083 (20 years)

Thanking anyone who able to assist in advance.

Happy New Year!

Can you tell me the calculation I would use to calculate accrued vacation based on these rules?
Years of Continuous ServiceTotal Vacation per YearHire year Up to 9 days**1-4 years 12 days5 years to 10 years17 days 11 years18 days12 years19 days13 years20 days14 years21 days15 years22 days
** Vacation earned based on full months worked, after 90 days of employment. You do not earn vacation for a plan year if you are hired after October 1st of that year.

Hi Guys,

I have used the following formula to calculate this months income accrual - last months income accrual (thus calculating the monthly movement);

=CALCULATE(sum(Accrual[Accrual]),PARALLELPERIOD(Accrual[Month],0,month))-CALCULATE(sum(Accrual[Accrual]),PARALLELPERIOD(Accrual[Month],-1,month))

It works great when looking at a monthly summary (ie just months as row labels) but when I add contract to the row labels iand move months to column labels it doesn't work in every case.

The problem arrises when a contract has no record for one of the particular months from which the calculation is based but If I have the accrual in both months for a particular contract then it works fine.

As a consequence, the grand total at the bottom of the pivot table is correct but if you some the variance for the contracts the total isn't correct because of the problem explained above.

Is there any way to get around this?

Thanks

I need to make a worksheet that will calculate vacation accruals. The accruals are: .083 hours a month up to 5 years and 1.88 hours a month after 5 years. I think I would use the if function but I have never used that one before and don't know the proper way to set it up.
Thanks for any help.

I know this has been discussed in previous posts, but every PTO policy is different.

I'm putting together a spreadsheet for a start-up client of mine. The would like to see every employee's current PTO balance (approx. 20 people). Employees begin accruing time immediately upon date of hire and earn 4 hours of PTO every pay period (they're paid semi-monthly), for a max of 96 hours per year. I would like to determine their exact amount based on their start date, and how much they've taken.

I can't imagine the formula would be that involved, but would like to have it set up like the attached spreadsheet.

Thanks in advance!

Hello All,

This is my first time posting but I have looked and looked and I'm now pulling my hair out. I need a spreadsheet to calculate vacation accruals based on hire date. I would like columns that include hire date, today's date, hours accrued. As some employees will only take 1/2 days off, I would like the pay period columns to work with numbers = 4 instead of X's. Here are our parameters:

26 Pay Periods - need the sheet to be based on pay periods
90 Days till vacation accrual begins
1-3 years = 3.0769 / pay period (10 days per year)
4-9 years = 4.6154 / pay period
10-14 years = 5.8462 / pay period
15 years = 6.9231 / pay period
Max is at 180 hours or 22.5 days per year.

I have a formula from someone else's sheet that I have tried to modify but I can't figure out where to change the pay periods which I would change before I modified the hours (at the end of the formula.) I am attaching the sheet - please look at the formulas in column F and I.

I sure do appreciate any help you guru's can give me!!!

Sherry

=IF(TODAY()-C31<90,0,MIN(180,DATEDIF(C31,TODAY(),"M")*CHOOSE(SUMPRODUCT(--(DATEDIF(C31,TODAY(),"M")>{0;1;2;3}*12)),2,2.15,2.3,2.46,2.61,2.76,2.92,3.07,3.23)))

Vacation Accrual.xls

Hello Everyone,

I have a nested IF function question. I have 9 columns. The first column (A)
is the name of an employee. The 2nd (B) -8th (H) columns are days of the
week. The 9th column is for tallying PTO hours (paid time off).
For example - Normally there is an S (swing shift) in each cell of row 225,
but if the employee wants to take PTO they replace the S with PTO. In column
9 (I225 I have a beginning number...say 40 (hours of accrued PTO)

in I246 I have this formula:
(equal sign)
IF(B225="PTO",I225-8,I225+0.44,IF(C225=”PTO”,I225-8,I225+0.44,IF(D225=”PTO”,I225-8,I225+0.44,IF(E225=”PTO”,I225-8,I225+0.44 continue 3 more times)))))))

I246 should look at I225 for the base number then look at row 225 for any
PTO's. It should subtract 8 hours from the number in I225 for every PTO it
finds, and it should add 0.44 (per day PTO accrual rate) for every S it finds.
I know you can't have more than 7 IF functions in a nest, so this should
work out great, but it doesn't. The first IF statement works,(by itself) but
once you add the other IF's then it gives me an error.

Thank you in advance for any help.

Rob

Hello

i am going to do my best to explain how i may require your help building a formula.
on the attached document, you will see a very simple calculation for accrual formula that add and withdraw amount from a month output (colomn c)at each of every three months (march, june, sept, dec)
this document attached works when its a jan2012 thru dec 2012 12 month basis.
but what if i use the same numbers on row C and now use from may 2012 thru april 2013? how can i have a formula that will give me the exact same total?
can u please help me ?? i am desperate, i m actually beyond desperation

Formula for Calculating (Accrual) Off Time (PTO)
I need to develop a tracking spreadsheet (for 18 employees) that will calculate accruing vacation time (on one worksheet) based on the following parameters:

Employees who have worked less than 1 year with the company:
- 10 days
Employees who have worked at least 1 year with the company:
- 12 days
Employees who have worked at least 2 year with the company:
- 13 days
Employees who have worked at least 3 year with the company:
- 14 days
Employees who have worked at least 4 year with the company:
- 15 days
Employees who have worked at least 5 year+ with the company:
- each year will add 1 day until reach the max 20 days

In addition I need to be able to deduct vacation time used. Does anyone have any suggestions for layout or for a formula that can do part of these functions? I appreciate any advice!

Hello,

I have tried several attempts on this board from what others have posted, but i have had no luck. PTO is a strange animal and when you are a small company, with small means, it becomes difficult to always find the best solution. We calculate like so:

Years 1 and 2 total max pto of 152 hours; Per Pal Accruual assuming 80 hrs worked, 5.85 for a total Max earned of 19
year 3 total max pto pf 176; 6.77 hours per 80 hours worked; 22 total per year
year 4 total max pto 192; 7.39 hours per 80 hours worked; 24 days per year
year 5 and on total max PTO 208; 8 hours per 80 worked; 26 total days per year

other issue, a persone can carry over up to 30 days annually or what they call bank PTO hours. can some one assist with a formula for this?

I think i am close but i have tried these,

=CHOOSE(IF(DATE(YEAR(B6)+5,MONTH(B6),DAY(B6))=20,A1*0.0375,IF(A1>=15,A1*0.025,0)))

So i am stuck in the middle. Please assist.

I have set up our PTO workbook but am having a problem. Is it possible to setup the worksheet so that it automatically converts the accrual rate. So on 09/07/11 it stops accruing at the .2.4615 rate, without losing that ending balance it picks up and adds the new accrual rate of 4.000 from that point on?

Then at the end of the year (12/31/11) it stops the accrual rate so that when a new sheet for 2012 is started it can calculate the appropriate balance from the previous year?

Attached is the workbook.
PTO Workbook 2011.xlsx

Thanks

Please see my attached spreadsheet.

Is it possible to setup the worksheet so that it automatically converts the accrual rate. So on 8/1/11 it stops accruing at the .833 rate, without losing that ending balance it picks up and adds the new accrual rate of 1.25 from that point on.

Then at the end of the year (12/31/11) it stops the accrual rate so that on the next sheet for 2012 it can calculate the appropriate balance from the previous year?

I would like to eliminate the "Accrued Days Until" and "Accrued Days After" fields if possible. I'd like to view the current accrual, not the possible accrual.

Hi Guys,
I'm trying to calculate a monthly leave accrual formula for staff on a monthwise table.

In the sample file attached, John has joined on 01-NOV-2010 so his leave accrual will start from Nov @ 2.5 per month. But I would like to have a formula that returns accrual as zero for the months prior to his joining.

Similarly, Mary has joined on 15-Dec-2010 so in the joining month of Dec2010 her leave accrual will be (2.5/31 x 17 days of service in Dec) and for the following months it will be 2.5 per month

Like Ali, who joined on 17-Feb-2011 wil have the zero accruals till the month of Jan and in Feb it will be (2.5/28 x 12days of service in Feb )

I hope the sample is clear. Any remote soultions to build on will be helpful.
Thanks


No luck finding an answer? You could always try Google.