+ Reply to Thread
Results 1 to 17 of 17

PLEASE HELP!!! Excel Spreadsheet to calculate monthly vacation/sick time accruals

  1. #1
    Registered User
    Join Date
    11-20-2012
    Location
    Boston, MA
    MS-Off Ver
    Excel 2010
    Posts
    9

    PLEASE HELP!!! Excel Spreadsheet to calculate monthly vacation/sick time accruals

    Attachment 195202Hi Everyone,

    I am new to the forum so thank you for any time you have taken out of your day to read and/or assist me.

    I've been searching through all the past threads and have done extensive research and I haven't been able to find a formula that will fit my needs so I'm hoping somebody can help. I work for a University so our vacation/sick time accrual is based on the academic year, which is currently July 1, 2012 - June 30, 2013. All of the formulas I have seen work for January to December, however I could be looking at it incorrectly as I am not the most sufficient in Excel.

    So basically, I need to create a calculation spreadsheet for the Department that I can forward to other supervisors for their own personal use for their employees based upon the following accrual/allottment rates:

    VACATION

    Years of Service: Up to 4 years of completed service/ Monthly Vacation Accrual: 1.25 days/ Annual Maximum: 15 days
    Years of Service: Over 4 years of completed service and less than 14 years of service/ Monthly Vacation Accrual: 1.67 days/ Annual Maximum: 20 days
    Years of Service: Over 14 years of completed service/ Monthly Vacation Accrual: 2 days/ Annual Maximum: 24 days

    SICK

    Years of Service: Up to 2 years of completed service/ Sick Days Allotted: 22 days/ Days Carried Over to Next Year: None
    Years of Service: Over 2 years of completed service and less than 7 years of service/ Sick Days Allotted: 44 days/ Days Carried Over to Next Year: None
    Years of Service: Over 7 years of completed service/ Sick Days Allotted: 66 days/ Days Carried Over to Next Year: None
    ** Employees allowed to use 3 accrued sick days for personal days.

    I've included a template where the supervisor can notate what was taken on which day for record keeping purposes (for example, a V on Jul 1), and then enter the number of days taken in each category for the month. I like my template but cannot for the life of me figure out how to enter these formulas. I've worked really hard on this so if there is any kind soul who is willing to help me figure this out, it would be GREATLY appreciated. Please feel free to contact me with any questions. Happy Holidays!
    Last edited by Madeira.K; 11-20-2012 at 04:22 PM.

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: PLEASE HELP!!! Excel Spreadsheet to calculate monthly vacation/sick time accruals

    I'm not sure how you intend to use this - will it be printed out, for a supervisor to manually enter values into it? I ask that because you say that the supervisor could enter the number of days in each category each month, but as this is a spreadsheet you could have a formula to work that out, eg put this in AG10:

    =COUNTIF($B10:$AF10,AG$9)

    which can be copied across and down that column and the other columns for totals of the categories. It is not clear how you would cope with just a half-day's sickness or leave.

    In order to work out the amount of vacation or sickness accrued, you will need to record the length of service for each employee, though there doesn't seem to be anywhere reserved for this. A good way of calculating this would be to have a table which summarised your "rules" above, and then use a VLOOKUP formula to get the amounts allowed.

    Hope this helps.

    Pete

  3. #3
    Registered User
    Join Date
    11-20-2012
    Location
    kettering england
    MS-Off Ver
    Excel 2000
    Posts
    15

    Re: PLEASE HELP!!! Excel Spreadsheet to calculate monthly vacation/sick time accruals

    The person's years of service can automatically define entitlements, and years of service can be calculated from the date the employee joined you. For each person I assume you want to enter holiday and sick as it occurs, and then maintain a running total - is this correct?

  4. #4
    Registered User
    Join Date
    11-20-2012
    Location
    Boston, MA
    MS-Off Ver
    Excel 2010
    Posts
    9

    Re: PLEASE HELP!!! Excel Spreadsheet to calculate monthly vacation/sick time accruals

    Hi Pete!

    Thank you very much for looking into this for me. This will be for a supervisor to keep on their computer to enter the values manually. Everything you brought up I had not even thought about so I am going to have to "play around" with it and see if I can figure this out. The joys of being an assistant :/

  5. #5
    Registered User
    Join Date
    11-20-2012
    Location
    Boston, MA
    MS-Off Ver
    Excel 2010
    Posts
    9

    Re: PLEASE HELP!!! Excel Spreadsheet to calculate monthly vacation/sick time accruals

    That is correct theoldebrewer!

  6. #6
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: PLEASE HELP!!! Excel Spreadsheet to calculate monthly vacation/sick time accruals

    Hi Madeira and welcome to the forum,

    You need a start date for each employee to determine how long they have worked to do your calculation. I don't see that anywhere on your example worksheet.

    If that was there then you might use a VLookup table to return Vacation and/or Sick days allowed.
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  7. #7
    Registered User
    Join Date
    11-20-2012
    Location
    Boston, MA
    MS-Off Ver
    Excel 2010
    Posts
    9

    Re: PLEASE HELP!!! Excel Spreadsheet to calculate monthly vacation/sick time accruals

    That definitly makes sense Marvin especially since everything is based on the start date haha. One of the employee's start date is 2/7/12. I'll have to insert that on the worksheet. My problem is, I've had the basic training in Excel, but not familiar with the VLookup table. I'll have to take a look at a tutorial on it.

  8. #8
    Registered User
    Join Date
    11-20-2012
    Location
    Boston, MA
    MS-Off Ver
    Excel 2010
    Posts
    9

    Re: PLEASE HELP!!! Excel Spreadsheet to calculate monthly vacation/sick time accruals

    I entered a column on the spreadsheet for the start date.

  9. #9
    Registered User
    Join Date
    11-20-2012
    Location
    Boston, MA
    MS-Off Ver
    Excel 2010
    Posts
    9

    Re: PLEASE HELP!!! Excel Spreadsheet to calculate monthly vacation/sick time accruals

    I cannot for the life of me figure this out. Thank you everyone for your help but I have officially given up

  10. #10
    Registered User
    Join Date
    11-20-2012
    Location
    India
    MS-Off Ver
    Excel 2003
    Posts
    3

    Re: PLEASE HELP!!! Excel Spreadsheet to calculate monthly vacation/sick time accruals

    Hi Madeira.K,

    please update the detailed stmt of source data & sample output data. so that we can easily understand the issue.

  11. #11
    Registered User
    Join Date
    11-20-2012
    Location
    Boston, MA
    MS-Off Ver
    Excel 2010
    Posts
    9

    Re: PLEASE HELP!!! Excel Spreadsheet to calculate monthly vacation/sick time accruals

    I'm not exactly sure what you mean. Please know that is not me trying to sound dumb, I'm just new at this but am very interested in learning.

  12. #12
    Registered User
    Join Date
    11-20-2012
    Location
    Boston, MA
    MS-Off Ver
    Excel 2010
    Posts
    9

    Re: PLEASE HELP!!! Excel Spreadsheet to calculate monthly vacation/sick time accruals

    Basically I need to be able to calculate vacation and sick time accruals based on the date of hire and the academic calendar of July 1 through June 30. I attached my draft spreadsheet but don't exactly know what to do with it, if that makes any sense whatsoever.

  13. #13
    Registered User
    Join Date
    11-20-2012
    Location
    India
    MS-Off Ver
    Excel 2003
    Posts
    3

    Re: PLEASE HELP!!! Excel Spreadsheet to calculate monthly vacation/sick time accruals

    Good to hear for your interest, please maintain some patience first.

    question (1) where is the source data?

  14. #14
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: PLEASE HELP!!! Excel Spreadsheet to calculate monthly vacation/sick time accruals

    Hi Madeira,

    You gave up too early. I've been working on this one problem for about a year and a half.

    Excel works best when you have it in TABLES of data. Start learning more about Excel and how it works and I'm sure you will find an answer, but never simply "give up".

  15. #15
    Registered User
    Join Date
    11-20-2012
    Location
    India
    MS-Off Ver
    Excel 2003
    Posts
    3

    Re: PLEASE HELP!!! Excel Spreadsheet to calculate monthly vacation/sick time accruals

    Hi MarvinP,

    Madeira is unable to explain the source data. bcz based on the source data format we can know the actuals.. Am i right. or is there any other deviation from my side?

  16. #16
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: PLEASE HELP!!! Excel Spreadsheet to calculate monthly vacation/sick time accruals

    Hi,

    Here is how I'd start doing this problem. See the attached. I'd put down the Name of the person and the reason for their non-attendance. Then I found a DateDif formula that will return the number of years they have served at: http://www.cpearson.com/excel/datedif.aspx that worked great in this problem. So if you change the Start Dates for either Name1 or Name2 the years are calculated correctly. Then I made two lookup tables, one for Vaca and another for Sick. If you use the TRUE parm in the VLookup function they work great.

    Lastly I created a Pivot Table to show dates and years. This is all to give you some ideas on how I'd approach this problem.
    See if any of the formulas or stuff works for you.
    Attached Files Attached Files

  17. #17
    Registered User
    Join Date
    11-20-2012
    Location
    Boston, MA
    MS-Off Ver
    Excel 2010
    Posts
    9

    Re: PLEASE HELP!!! Excel Spreadsheet to calculate monthly vacation/sick time accruals

    It's not that I am giving up on my project, it's moreso I am entry-level compared to the members of this forum so I don't necessarily understand the verbiage. Although I am an educated individual, I need to study more because I hope one day to get to everyone else's level of expertise.

+ 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