+ Reply to Thread
Results 1 to 7 of 7

Calculating Sick/Personal Leave

  1. #1
    Registered User
    Join Date
    10-22-2012
    Location
    Minnesota
    MS-Off Ver
    Microsoft Excel 2010
    Posts
    12

    Question Calculating Sick/Personal Leave

    My manager has asked me to set up a timeroll speadsheet for 2013 because we currently keep track on paper. I currently use Excel 2010 I have set up a workbook for all full time employee. Within this workbook I have figured out my formulas for days earned which will decrease as days are used. All employees get 15 sick days a year and out of those 15 days 3 can be used as personal days. I am very confused as how to continue my formual to show if a day of personal days are used this also need to be subtracted from sick days. Below are my current formulas for sick and personal days starting in January 2013. I have also attached my workbook for better understanding.

    B6=(c6-aj6)/8
    Sick Days Remaining=15 sick days a year-sick days used for the month
    B7=(c7-aj7/8)
    Personal Days Remaining=3 Personal days a year-personal days used for the month

    Please help as I have to submit this by December 2012.
    Thank you,
    Attached Files Attached Files
    Last edited by ssaurer; 10-23-2012 at 10:17 AM.

  2. #2
    Registered User
    Join Date
    10-21-2012
    Location
    Hong Kong
    MS-Off Ver
    Excel 2003
    Posts
    45

    Re: Calculating Sick/Personal Leave

    I get few points from your worksheet and your description:
    1)The leaves count as "hours", and each 8 hour treat as one day used.
    2)Each month uses a sheet, there will have situation a month record will use last month's result.

    Here is what I suggest, you can have a try to let me know if it works.
    In Sheet "Jan 2013"
    B6:
    Please Login or Register  to view this content.
    B7:
    Please Login or Register  to view this content.
    In Sheet "Feb 2013"
    B6:
    Please Login or Register  to view this content.
    B7:
    Please Login or Register  to view this content.

  3. #3
    Registered User
    Join Date
    10-22-2012
    Location
    Minnesota
    MS-Off Ver
    Microsoft Excel 2010
    Posts
    12

    Re: Calculating Sick/Personal Leave

    This works however it only subtracts if an employee takes personal or sick days indvudially. I am more looking for if a personal day is used this needs to be subtracted from the personal days remaing and also from the sick days remaing as we have 15 sick days and 3 of those days can be used for personal. There has to be a way to extend my formula in my Sick Days remaing cell (B6)to subtract if any of personal days (B7) were used.
    Thank you

  4. #4
    Registered User
    Join Date
    10-21-2012
    Location
    Hong Kong
    MS-Off Ver
    Excel 2003
    Posts
    45

    Re: Calculating Sick/Personal Leave

    I'm confused about the logic of the subtraction of sick day and personal day. Can you give me some example about this?

    Do you mean, if personal days are used BUT there's no personal day remains THEN sick days will be subtracted right?

    If that's the case, then:
    In Sheet "Jan 2013"
    B6:
    Please Login or Register  to view this content.
    B7:
    Please Login or Register  to view this content.
    In Sheet "Feb 2013"
    B6:
    Please Login or Register  to view this content.
    B7:
    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    10-22-2012
    Location
    Minnesota
    MS-Off Ver
    Microsoft Excel 2010
    Posts
    12

    Re: Calculating Sick/Personal Leave

    I mean if personal days are used then sick days will also be subtracted. Employees have 15 sick days a year an employee may use up to a maximum of 3 days per year from their annual sick leave as personal days. I somehow need to extend my Sick Leave remaining balance cell B6 to also subtract if any personal hours were taken.
    Cell C6=120 hours of yearly sick leave(Cell B6=15 days(converted hours to days)):
    Cell C7=24 hours of personal leave (B7=3 days(converted hours to days)):if these three days are used througout each month they need to be subtracted from C7 and also from the C6.
    Note: 8 hours = one day

    Thank you,
    Last edited by ssaurer; 10-24-2012 at 01:04 PM.

  6. #6
    Registered User
    Join Date
    10-21-2012
    Location
    Hong Kong
    MS-Off Ver
    Excel 2003
    Posts
    45

    Re: Calculating Sick/Personal Leave

    So, you want a return of both remaining sick/personal day and their hours?

    I think my code in #4 can suit the need as it has considered the done used of personal leave effect on sick leave.
    Try it.

  7. #7
    Registered User
    Join Date
    10-22-2012
    Location
    Minnesota
    MS-Off Ver
    Microsoft Excel 2010
    Posts
    12

    Re: Calculating Sick/Personal Leave

    This does work now I must have orginally typed something incorrectly. - Thank you Nels for taking the time to help me.

+ 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