+ Reply to Thread
Results 1 to 22 of 22

Formula for Calculating (Accrual) Vacation Time

  1. #1
    Registered User
    Join Date
    01-27-2009
    Location
    New York
    MS-Off Ver
    Excel 2003
    Posts
    1

    Formula for Calculating (Accrual) Vacation Time

    I need to develop a tracking spreadsheet (for 12 employees) that will calculate accruing vacation time (on one worksheet) based on the following parameters:

    Employees who have worked less than 3 years with the company:
    - 2.91 hours biweekly
    Employees who have worked more than 3 years but less than 8 years with the company:
    - 4.38 hours biweekly
    15 years and over with the company:
    - 6.41 hours biweekly

    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!

  2. #2
    Registered User
    Join Date
    12-30-2008
    Location
    Vermont, USA
    MS-Off Ver
    Excel 2003
    Posts
    64

    Re: Formula for Calculating (Accrual) Vacation Time

    Attached is something to get you started--
    Just enter employee names in place of the Employee1, Employee2, etc.
    -enter start date for each employee
    -enter hours used for each employee on rows 5 through 104 (if you need more space, just edit the SUM portion of the formula in the Vac_avail row to include more, for instance, ...SUM(B5:B2004) would give you 2000 spaces
    -edit the formula in Vac_Rate to give a rate to the poor souls who have worked at least 8 but less than 15 years (you didn't mention these people in your post, so I just made up a rate of 5.5 hrs./14 days--change the 5.5 to whatever)
    -the formulas assume that no vacation time is lost at the end of a year.
    -entering a negative number in the Vac_Used rows will, in essence, award more time to that employee which may be useful-- these will show up in red
    -if an employee has used more than allowed, their vac_avail will show up in red also
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    12-30-2008
    Location
    Vermont, USA
    MS-Off Ver
    Excel 2003
    Posts
    64

    Re: Formula for Calculating (Accrual) Vacation Time

    actually- small mistake; use this one
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    12-30-2008
    Location
    Vermont, USA
    MS-Off Ver
    Excel 2003
    Posts
    64

    Re: Formula for Calculating (Accrual) Vacation Time

    actually, it occurs to me that you will need a slightly more sophisticated formula for vacation accrual, because as it currently is, when a person passes the employment milestones (3 yrs, 8yrs, etc), the vacation they previously accrued (before the milestone) gets inflated also, which isn't what you want (though it would be nice for the employees). Don't have time at the moment, but perhaps someone else does? Or you can give it a try yourself.

  5. #5
    Registered User
    Join Date
    03-26-2011
    Location
    Kansas
    MS-Off Ver
    Excel 2003
    Posts
    1

    Formula for Calculating (Accrual) Vacation Time on a bi-weekly basis

    I am in need of some serious help. I have looked in the four corner of the earth and cannot find a solution to my problem.

    I am trying to create a spreadsheet to calculate vacation accural for my employee's.
    I have reapeatedly tried to create formulas that are not doing what I need them to do.

    Here is what I am needing:

    Employee gets 2 hours per pay (bi-weekly) for 0 - 1year employed

    Employees that are employed 1 year and beyond accrue 3 hours per pay (bi-weekly)
    the max is 2 weeks or 80 hours that is allowed no matter how long you are employed.

    Also vacation time does not expire at all.

    This is of course all based on there hire date.

    I am very new to the formula world so I will need a idiots explanation please.

  6. #6
    Registered User
    Join Date
    10-02-2011
    Location
    United States
    MS-Off Ver
    Excel 2007
    Posts
    1

    Re: Formula for Calculating (Accrual) Vacation Time

    Please help.

    I am a salaried employee paid twice a month. I am in desperate need of a spreadsheet to track my own time. I accrue 1.25 hours each month and receive 6 personal days each year. I do okay with spreadsheets but really need some help here. I would like a spreadsheet that would include formulas to recalculate my time when it's used. I don't have a problem with entering the 1.25 each month. My vacation time does not expire.

    Any assistance would be greatly appreciated. Thanks in advance!

  7. #7
    Forum Expert
    Join Date
    12-23-2006
    Location
    germany
    MS-Off Ver
    XL2003 / 2007 / 2010
    Posts
    6,326

    Re: Formula for Calculating (Accrual) Vacation Time

    Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread.

  8. #8
    Registered User
    Join Date
    11-12-2012
    Location
    Sunnyvale, CA
    MS-Off Ver
    Excel 2004 for Mac
    Posts
    7

    Re: Formula for Calculating (Accrual) Vacation Time

    Quote Originally Posted by clownfish View Post
    actually- small mistake; use this one
    hello this spreadsheet was super helpful for me but have one question. i actually need to change the formula from the TODAY to a specific date, 3/15/2013. i only need to figure out the pto time accrued from the before that date. also, for my company, everyone gets the same amount of pto accrual of 3.07 hours per pay period except for one employee who gets 3 weeks, i have not figured out the pay period breakdown.

    help please, how do i change the Today part of the formula to a specific date?

    thank you

  9. #9
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,570

    Re: Formula for Calculating (Accrual) Vacation Time

    dangerme
    Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

    If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

    Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.
    Ben Van Johnson

  10. #10
    Registered User
    Join Date
    09-25-2013
    Location
    new jersey
    MS-Off Ver
    Excel 2003
    Posts
    2

    Re: Formula for Calculating (Accrual) Vacation Time

    We offer PTO to parttime employees. 2 Weeks after one year 3 weeks after five years. I have a parttime employee who works 3 days a week 7 hours per day. She is a 10 month only employee. She also did not earn her 3rd week until Sept. 1. How would I calculate how much time she has accrued?

  11. #11
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Formula for Calculating (Accrual) Vacation Time

    lizf

    Welcome to the forum.

    Pls see post$9!
    Regards

    Fotis.

    -This is my Greek whisper to Europe.

    --Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.

    Advanced Excel Techniques: http://excelxor.com/

    --KISS(Keep it simple Stupid)

    --Bring them back.

    ---See about Acropolis of Athens.

    --Visit Greece.

  12. #12
    Registered User
    Join Date
    09-25-2013
    Location
    new jersey
    MS-Off Ver
    Excel 2003
    Posts
    2

    Re: Formula for Calculating (Accrual) Vacation Time

    Thanks I'm new to the site and not sure I posted in the correct area. Just trying to get some helpful information, I am new in this position. Trying to figure out this parttime vacation accrual.

  13. #13
    Registered User
    Join Date
    01-10-2014
    Location
    Voorhees, New Jersey
    MS-Off Ver
    Excel 2007
    Posts
    1

    Re: Formula for Calculating (Accrual) Vacation Time

    This was extremely helpful to me. Thank you so much. Made my day.

  14. #14
    Registered User
    Join Date
    06-24-2014
    Location
    Chicago
    MS-Off Ver
    Macintosh
    Posts
    1

    Re: Formula for Calculating (Accrual) Vacation Time

    When we have employees leave in the middle of the year or when we have employees start in the middle of the year - how do I calculate how many vacation days they get? Is there a simple excel formula or a simple function to figure this out?

    Generally we use: For example, an eligible staff employee (6 months or more of service) with a standard workweek of 5 days per week accrues two (2) standard workweeks of vacation per year: 5 days per week x 2 weeks per year (10) ÷ 12 months per year = 8.5 hours per month. -

    But working this out, I get easily confused with all the numbers.

    Any help is appreciated.

  15. #15
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,443

    Re: Formula for Calculating (Accrual) Vacation Time

    Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

    If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

    Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.

  16. #16
    Registered User
    Join Date
    10-29-2014
    Location
    Indiana
    MS-Off Ver
    Office 365
    Posts
    2

    Re: Formula for Calculating (Accrual) Vacation Time

    Ok... Need help!! I know the extreme basics of formulas in excel. I have a growing need for a better way to keep up with my employees vacations.

    I have just over 120 employees.... They earn 1 week of vacation after one year (from their start date) of working. The vacation time does not rollover and I have varying lengths of weekly amounts (i.e. 40 hours, 35, 30, 20, etc...) I just need to be able to keep track of the dates they take the vacations and for the amount to deduct once they take a day/week of vacation. HOW do I accomplish this??

  17. #17
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Formula for Calculating (Accrual) Vacation Time

    indy, welcome to the forum

    Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

    If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

    Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  18. #18
    Registered User
    Join Date
    03-04-2015
    Location
    atlanta, ga
    MS-Off Ver
    MS Office 2013
    Posts
    2

    Re: Formula for Calculating (Accrual) Vacation Time

    I am actually just trying to keep up with my PTO time. According to our hand book it has nothing regarding PtO time, accrued rate for Part time employees. I want to figure out for each pay period by the hours worked how many PtO hours I have earned.

  19. #19
    Registered User
    Join Date
    12-25-2019
    Location
    Philippines
    MS-Off Ver
    2010
    Posts
    2

    Re: Formula for Calculating (Accrual) Vacation Time

    ***started a new thread..sorry

    Help please. I'm trying to create a tracker. I need to calculate how many accrued SL per month will each employee have if we are given 7 SL in a year. I can't seem to figure out the formula. Thank you.
    Last edited by dctrp; 01-20-2020 at 04:56 PM. Reason: Started a new thread

  20. #20
    Registered User
    Join Date
    01-26-2022
    Location
    Colorado, United States
    MS-Off Ver
    2112 excel
    Posts
    3

    Re: Formula for Calculating (Accrual) Vacation Time

    Please Help! I am trying to create a tracker. I need to calculate how many accrued PTO per year each employee will get. I am struggling to figure out the formula.
    This is how it is explained.
    • PTO is not accrued between the employee’s hire date and December 31st of the employee’s hire year.
    • 1st full calendar year after hire, PTO is not accrued.
    • 2nd full calendar year after hire, PTO is accrued at a rate of 2 days (16 hours) per month, not to exceed 4 days (32 hours).
    • 3rd full calendar year after hire, PTO is accrued at a rate of 2 days (16 hours) per month, not to exceed 6 days (48 hours).
    • 4th full calendar year after hire, PTO is accrued at a rate of 2 days (16 hours) per month, not to exceed 7 days (56 hours).
    • 5th full calendar year after hire, PTO is accrued at a rate of 2 days (16 hours) per month, not to exceed 8 days (64 hours).
    • 6th full calendar year after hire, PTO is accrued at a rate of 2 days (16 hours) per month, not to exceed 9 days (72 hours).
    • 7th full calendar year after hire, PTO is accrued at a rate of 2 days (16 hours) per month, not to exceed 10 days (80 hours).
    • 8th full calendar year after hire, PTO is accrued at a rate of 2 days (16 hours) per month, not to exceed 11 days (88 hours).

  21. #21
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Formula for Calculating (Accrual) Vacation Time

    Quote Originally Posted by cbomb920 View Post
    Please Help! I am trying to create a tracker. I need to calculate how many accrued PTO per year each employee will get. I am struggling to figure out the formula.
    This is how it is explained.
    • PTO is not accrued between the employee’s hire date and December 31st of the employee’s hire year.
    • 1st full calendar year after hire, PTO is not accrued.
    • 2nd full calendar year after hire, PTO is accrued at a rate of 2 days (16 hours) per month, not to exceed 4 days (32 hours).
    • 3rd full calendar year after hire, PTO is accrued at a rate of 2 days (16 hours) per month, not to exceed 6 days (48 hours).
    • 4th full calendar year after hire, PTO is accrued at a rate of 2 days (16 hours) per month, not to exceed 7 days (56 hours).
    • 5th full calendar year after hire, PTO is accrued at a rate of 2 days (16 hours) per month, not to exceed 8 days (64 hours).
    • 6th full calendar year after hire, PTO is accrued at a rate of 2 days (16 hours) per month, not to exceed 9 days (72 hours).
    • 7th full calendar year after hire, PTO is accrued at a rate of 2 days (16 hours) per month, not to exceed 10 days (80 hours).
    • 8th full calendar year after hire, PTO is accrued at a rate of 2 days (16 hours) per month, not to exceed 11 days (88 hours).
    Administrative Note:

    Welcome to the forum.

    We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

    Please see Forum Rule #4 about hijacking and start a new thread for your query.

    If you are not familiar with how to start a new thread see the FAQ: How to start a new thread

  22. #22
    Registered User
    Join Date
    01-26-2022
    Location
    Colorado, United States
    MS-Off Ver
    2112 excel
    Posts
    3

    Re: Formula for Calculating (Accrual) Vacation Time

    created by mistake
    Last edited by cbomb920; 01-27-2022 at 03:37 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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