Free Microsoft Excel 2013 Quick Reference

Vacation accrual calculation formula Results

Dear Gurus,
I have attached a partial vacation accrual worksheet I would like to modify. I would like to modify the formula in column N so that if somebody is terminated in the first 15 days of a month and that date is entered in column J, they get 1/2 of the month's accrual, and then it stops. If the are terminated after the 16th, they get a full month's accrual and then it stops.
All help is appreciated. Thanks!

I inherited a problem, as usual, lol. I've been tasked with a project to adjust Vacation time to the correct amounts because whoever was doing this before me neglected to adjust employees Vacation per their Limits at Calendar Year End. So here we are in the middle of the year trying to determine which employees were over their limit in Jan08 and how much we have to take away from them now to correct that oversight, ugh! We use QB Enterprise Vs 6 and you can not get this information historically. You can look at an employee's check detail from last year and it shows the current amounts for everything. Below is what I have determined so far............

Known data:
1. Vacation Accrual Rate X Number of PayDays (13 for us at this point) = Current Year Earned
2. System provides Vacation Used (accummulated, not just this year), I have a spreadsheet that has this years information in separate sheets for each pay period
3. System provides Vacation Available
4. Limit based on Exempt (160)/NonExempt (120) code

Unknown data:
1. Vacation Available 1st payday Jan08
2. once we know what they actually had, we can do an if statement to compare what they had Available to their Limit and IF they are over, subtract the limit to get the amount that should be subtracted from their Current Vacation Available
3. Subtract the result from 2 above from Current Available to give us the correct amount available

2 other people, one supposedly with a 4 yr degree in Accounting, and one is supposedly a CPA and neither of them felt confident with what they came up with. If you have used QB Enterprise, you know how confusing their software can be, and how limited it can be in some areas. They have an option for setting the Limit but it will not let the employee earn more than the limit so we can't use that feature or else there would be no problem, lol.

Whatever I come up with HAS to be right because we are taking away people's Vacation time so you can see how important it is to get this formula right the first time.

Thanks for your help in advance!!!
Barb

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

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 am attempting to create a spreadsheet that will list the number of vacation days earned per year by each employee. The earning rate is as follows:

0 to < 1 year = 15 days
1 to >= 7 years = 20 days
7 to >=15 years = 25 days
15 to >= 25 years = 30 days
>25 = 35 days

With the exception of the 0 to 1 year employees and the over 25 year employees, this formula yeilds the correct answer. Where G2 (37.31) lists the years of service. The problem I'm having is that I can not total this column. Once I have all the individual employee vacation earnings calculated, I need the sum of all vacation days for all employees. The answer my formula yeilds although correct is purely a text and will not total.

=IF(G2>=15,"30",IF(G2>7,"25",IF(G2>1,"20","")))

Sample
Name On Site Hire Date Other Today Srv Days Srv Yrs
Sterle, T 8/24/93 8/24/70 1/26/87 12/5/07 13617 37.31 30
Sterle, A 2/13/94 2/13/92 8/10/88 12/5/07 5774 15.82 25
Brangard 7/31/94 7/31/00 2/4/91 12/5/07 2683 7.35 25
Freoni 8/28/94 8/28/02 8/15/88 12/5/07 1925 5.27 20
Jackson 7/2/95 7/2/05 11/13/89 12/5/07 886 2.43 20

Any help would be greatly appreciated.

NPA Sr.

We have a client that has a complicated vacation policy and I just can't get my brain wrapped around how to write this one out. Any direction would be appreciated.

1) Employees must have been employed at least 1 year before becoming eligible for vacation.
2) On their anniversary date (every year), if they have worked an average of at least 30 hours a pay period for the year, then they receive an accrual equal to that average, with a max of 40 hours. (Formula for that would be total hours for year/52 weeks - does not take into consideration how many checks those hours were received over)
3) If that anniversary date has not occured yet, the most recent anniversary date has to be used (if the anniversary date is 10/01 and the hire year was 2004, then to determine vacation accrual right now, the records from 10/01/2007 to 9/30/2008 must be used to calculate the average hours)
4) Unused hours reset to 0 on the anniversay date.
5) Calculations also must be made going forward from the anniversary date to accrue any potential vacation.

I am assuming that I can do most of this in VBA (we have Excel 2007). I have attached a data sample.

Thanks for any and all suggestions! This is driving me crazy!
LFox

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,

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

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)

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!

Our office has a vacation days accrual policy based on number of years worked. We have a vacation days number, based on year of employment, the employee earns monthly. I need help with a worksheet, formulas, to document each employee, the year of employment they're in, # of days they have available based on the current month (which would need to add up automatically as the year progresses), then any days they request off, and finally a remaining balance of days left.

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?

Thanks
Nadine

--
MissNadine
------------------------------------------------------------------------
MissNadine's Profile: http://www.excelforum.com/member.php...o&userid=26394
View this thread: http://www.excelforum.com/showthread...hreadid=396920

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?

Thanks
Nadine

--
MissNadine
------------------------------------------------------------------------
MissNadine's Profile: http://www.excelforum.com/member.php...o&userid=26394
View this thread: http://www.excelforum.com/showthread...hreadid=396729

Hello all,
i am trying to figure out an excel formula to calculate vacation time here is my company's policy:
Vacation time does not roll over.

years of service monthly vacation accrual total days/year less than 6 0.83 10 6 through 10 1.25 15 11 or more 1.66 20

new hire must complete 6 months. Ee will earn 1 day per completed month until the end of the calendar year, at the beginning of the following year ee will use schedule above not to exceed 10 days. if employee was hired after july 1st he must wait until 3rd year to use the schedule above.

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?

Thanks
Nadine

I need assistance with the creation of a formula. I am creating an employee vacation accrual worksheet and I need a formula that will do the following:

return a value that equals the actual accrued vacation time for any employee on a monthly basis. I have attached a copy of the worksheet for your review. (see attachment)

In looking at the worksheet, I am currently taking the columns "months employed" and "accrual mode" to determine the actual number of hours accrued (see "hours accrued column). However, as the employee accrues their vacation hours each month they work, they cannot accrue hours greater than 160 hours in total. I attempted to use a MIN Function to do this while still calculating the SUM of the "months employed" and "accrual mode" columns. This Function was returning a value of 160 if the worksheet sum of the "months employed" and "accrual mode" exceeded 160. However, it was still calculating the SUM of the "months employed" and "accrual mode" columns figures in the background.

I need the worksheet to stop calculating when it reaches the the value of 160. In addition, when the employee has used any of their vacation hours, I need the worksheet to deduct their time used from the total value (160). This will undoubtedly cause the total number of accrued hours to fall below 160. I need the worksheet to see this and continue to calculate vacation hours when it detects the "hours accrued" column is less than 160 until it reaches the max figure or cap of 160 hours.

I hope I have provided as much information as you may need. Your assistance would be greatly appreciated.

This is a new system this year (2012) in our company for vacation: Our employees accrue vacation on their anniversary date based on tenure (7.5 hours up to 4 years, 11.25 hours up to 8 years, and 15 hours for all others). They accrue for only 10 months to get all available time; no vacation rolls over. Right now, all employees hired before December 31, 2011, have an anniversary date of January 1. All employees hired this year and moving forward have their hire date as an anniversary date. I have a spreadsheet that calculates accrued hours based on calculating the month we're in. However, I don't know how to account for no accrual in November and December. Neither do I know how to set up formulas for people whose anniversary is anything other than January 1. Can anyone help?

I need to create a formula that will calculate the amount of vacation time employees have based on their length of service with the company. Vacation time accrues monthly. Below is a breakdown of the accrual.

1.25 days accrual each month to employees 10+ years.83 days per month to employees 2+ years.42 days days per month employees 1+ years

Thanks for your help

Ok So I did not get an answer tha helpe dme in my previous question so I am
going to try it again. I am not sure if I explained what Iam looking for
correctly.

I am trying to make a formula to calculate an employee's vacation and sick
time accrual. Employees start accruing both after completing their first
sixty days. It is long so bare with me.......

VACATION
Each regular fulltime employee accrues 80 hours per year for the first five
years and 120 hours a year after five years of working service. Each part
itme employee accrues a percentage of the regular accrual based on what
percentage they work.EX-A part time employee who works 32 hours a week is
working 80% of a regular work scheduel and therefore recives 80% percent of
the vacation benefits.

SICK
Each regular fulltime employee accrues six days per year. Partime employees
accrue based on the percentage that they work as written above.

The accruals are calcualted according to the payroll schedule which is
semi-momthly so we have 24 payroll periods a year.

Both vacation and sick time accrual rollover at the end of the year.

I need an excel miracle for this!
--
Afroines

Help please!! I am finalizing a payroll accrual file and would love it if
there was a way to speed up this process every year.

Does any know the formula to get the remaining weeks in a year based on
various date fields?

In other words... an employee accruing his 1st 40hrs of vacation would be...
Emp#123(A1) started on 7/1/05 (A2) - therefore, his specific rate of accrual
..769231 (A3) would be calculated by the remaining weeks 26 (A4) in the year.
What formula can I use to find (A4) based on date of hire (A2) and 52 weeks
in a year?

Anyone's help would be greatly appreciated!!
Have a great day peeps.