+ Reply to Thread
Results 1 to 12 of 12

OMG Trying to Calculate Vacation and Sick Time

  1. #1
    Registered User
    Join Date
    03-18-2013
    Location
    Georgia
    MS-Off Ver
    Excel 2007
    Posts
    6

    Red face OMG Trying to Calculate Vacation and Sick Time

    I am dyin' here...

    I've attached a sample sheet I started 4 hours ago...need to calculate how much time everyone has in sick and vacation time. Don't believe they want it to go back to their start date. Simply calculate for the year but WHATEVER...just want to get it DONE!!!

    Here are the parameters

    Policy for Sick Time:

    Reg FT employees accrue sick leave at the rate of 5 days per year (.41666 days per month).
    Leave is used at a minimum of 4 hours

    Vacation policy:
    • employees working here under 10 years earn up to a max of 10 vacation days a year accrued at a rate of .8334 days per month
    • after 5 years employees earn up to a max of 15 vacation days each year accrued monthly at the rate of 10 hours per month
    • After 10 years the employee may earn up to a max of 20 vacation days each year, accrued monthly at the rate of 13.3334 hours per month

    Thanks to anyone who can help me with this!!!
    Attached Files Attached Files

  2. #2
    Forum Expert ConneXionLost's Avatar
    Join Date
    03-11-2009
    Location
    Victoria, Canada
    MS-Off Ver
    2010
    Posts
    2,952

    Re: OMG Trying to Calculate Vacation and Sick Time

    You seem to be mixing days and hours. Wouldn't it be simpler to keep everything in hours? If so, how many hours per day does your group work?
    Would you like to say thanks? Please click the: " Add Reputation" button, on the grey bar below the post.

  3. #3
    Registered User
    Join Date
    12-16-2012
    Location
    Austin, TX
    MS-Off Ver
    Excel 2010 & 2007
    Posts
    66

    Re: OMG Trying to Calculate Vacation and Sick Time

    Question on rate policies: Do the increased rates begin on their 5 and 10-year anniversaries, or do the rates change on January 1st of the year they would reach that anniversary?

  4. #4
    Registered User
    Join Date
    03-18-2013
    Location
    Georgia
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: OMG Trying to Calculate Vacation and Sick Time

    Thanks for taking the time to reply...I'm just taking it from the handbook. They base things on an 8 hour day (5 day work week)

  5. #5
    Registered User
    Join Date
    03-18-2013
    Location
    Georgia
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: OMG Trying to Calculate Vacation and Sick Time

    Anniversary day

    Thanks for the assistance

  6. #6
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    50,186

    Re: OMG Trying to Calculate Vacation and Sick Time

    Hi and welcome to the forum

    for the Sick Time, try this, copied down and across...

    =IF((((YEAR($A3)-YEAR(B$2))*12)+(MONTH($A3)-MONTH(B$2)))*0.416666<=0,0,(((YEAR($A3)-YEAR(B$2))*12)+(MONTH($A3)-MONTH(B$2)))*0.416666)
    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

  7. #7
    Registered User
    Join Date
    03-18-2013
    Location
    Georgia
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: OMG Trying to Calculate Vacation and Sick Time

    Thanks for the welcome but I don't understand where I paste this based on the spreadsheet I included with my original question. Forgive me. They've really thrown me with this project and it turns out they have never been able to figure it out themselves so they decided to put ME to the test.

  8. #8
    Registered User
    Join Date
    12-16-2012
    Location
    Austin, TX
    MS-Off Ver
    Excel 2010 & 2007
    Posts
    66

    Re: OMG Trying to Calculate Vacation and Sick Time

    I'm giving it a shot. I have questions about the policies:
    1) Do they accrue gradually over time (so a new employee starts with 0 days), or do they get time pre-loaded at the beginning of the year?
    2) If based on accrual, does someone stop accruing if they reach the cap?
    3) Can vacation days roll over from year to year, or do they get paid out/lost on Dec 31st?

  9. #9
    Registered User
    Join Date
    12-16-2012
    Location
    Austin, TX
    MS-Off Ver
    Excel 2010 & 2007
    Posts
    66

    Re: OMG Trying to Calculate Vacation and Sick Time

    The solution that FDibbins put in works, but you have to change the months in A3:A4 to actually be Date/Year (like 1/2013, 2/2013, etc).

    Then you can put his solution into B3, mouse over the bottom right-corner of the cell (so it turns into a plus), and then "drag" the formula all the way down to B14. Then you can drag the column's formula over to H3:H14.

    Caveats: It is giving you the total summation of all accumulated sick leave. It is not showing the balance, because nowhere on the sheet does it indicate what time they have taken.

    I'm working on the vacation part...I did a similar spreadsheet on here in the past, but even looking at, I can't remember how to use half the functions I used then! So...still working on it.

    Interestingly, your sheet gave Jen and Tina full sick benefits for the month of February, even thought they only worked part of it. FDiddens' formula gave them none for February, b/c it calculated they had worked 0 months. :P

  10. #10
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    50,186

    Re: OMG Trying to Calculate Vacation and Sick Time

    sorry, thanks for the catch, jkray, i did change them in my version, but forgot to mention it

  11. #11
    Registered User
    Join Date
    03-18-2013
    Location
    Georgia
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: OMG Trying to Calculate Vacation and Sick Time

    Quote Originally Posted by jkray622 View Post
    I'm giving it a shot. I have questions about the policies:
    1) Do they accrue gradually over time (so a new employee starts with 0 days), or do they get time pre-loaded at the beginning of the year?
    2) If based on accrual, does someone stop accruing if they reach the cap?
    3) Can vacation days roll over from year to year, or do they get paid out/lost on Dec 31st?

    New employees DO start with '0'
    Yes there is a cap
    No vacation/sick does NOT rollover - and it is based on their anniversary date NOT Dec 31

    Thanks so much for helping me out!!

  12. #12
    Registered User
    Join Date
    03-18-2013
    Location
    Georgia
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: OMG Trying to Calculate Vacation and Sick Time

    Quote Originally Posted by jkray622 View Post
    Question on rate policies: Do the increased rates begin on their 5 and 10-year anniversaries, or do the rates change on January 1st of the year they would reach that anniversary?
    Rates chg on their anniversary dates ... not Jan 1

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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