Free Microsoft Excel 2013 Quick Reference

- Vacation accrual calculation formula
- Vacation Time calculation
- How do I calculation vacation accrual?
- Formula for vacation accruals
- Vacation Accrual
- vacation accrual formula based on previous year avg hours worked
- Calculating Vacation Accruals
- Vacation Accruals Help PLEASE!
- Calculate vacation accrual based on hire date
- Calculate vacation accrual based on variable years of service
- Calculate Employee Vacation Accrual Rate & Number Of Days Taken/Left
- Please help!! Vacation Accrual Formula
- Vacation Accrual Formula
- Excel formula to calculate vaction time
- Please help!! Vacation Accrual Formula
- Formula continues to calculate in the background
- Help with vacation accrual
- Formula needed to calculate vacation time
- VACATION/SICK TIME ACCRUAL
- Excel - formula help needed

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!

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

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

Thanks for any help.

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.

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

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!

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

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)

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!

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

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

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.

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

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.

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

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

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.