+ Reply to Thread
Results 1 to 20 of 20

sick time accrual

  1. #1
    Registered User
    Join Date
    09-04-2017
    Location
    cape cod, ma
    MS-Off Ver
    10
    Posts
    11

    sick time accrual

    Hi I am trying to create a formula for part-time/full-time employees sick time accrual. The employee will get 1 hour for every 30 hours worked. I have a spreadsheet set-up that calculates that one line, but doesn't calculate the lines below to keep adding each line until it reaches 30 hours to earn that sick hour.

    I have attached a screen shot of the spread sheet i have made so far. I am not sure If i am making sense.

    Can anyone help? The rules are: every 30 hours worked= 1 hour; may carry-over 40 hours the next year (years are starting on 1/1 payroll period (monday thru sunday paid weekly every friday).


    sickaccrual.jpg
    Last edited by caitline23; 09-22-2017 at 11:53 AM.

  2. #2
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,427

    Re: sick time accrual

    It would help us to help you if you would upload a spreadsheet as opposed to a screen shot. I suggest that you include an example of how employee 1 would start the year if s/he had more then 40 hours of sick leave left at the end of 2016. Also show your expected results if employee 1 works less than 30 hours in a given week as in would the number of hours worked that week be added to the number of hours worked the next. Along the same lines show an example of expected results if employ 1 works more than 30 hours and if applicable more than 60 hours in a week.
    To upload a sample spreadsheet click on the GO ADVANCED button below the Quick Reply window and then scroll down to Manage Attachments to open the upload window.
    Let us know if you have any questions.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  3. #3
    Registered User
    Join Date
    09-04-2017
    Location
    cape cod, ma
    MS-Off Ver
    10
    Posts
    11

    Re: sick time accrual

    I am not sure if that attachment went through or not.

    But, any time not used in previous year would be rolled over; not sure how I can add that.
    If someone doesnt have a full 30 hours in the one week, those hours would be rolled over to the next week until that hour is earned (week 1, 26.5 hrs; week 2, 25 hrs= 51.50, 1 hour + 21.50; 21.50 gets rolled over to the next until another hour is accrued and so on)

    if someone has overtime/more than 40 hours, it continues to add (58.80 hrs in week one; 50 hrs week 2= 108.80= 3.63 sick hours earned)

    I hope this actually makes sense.
    Attached Files Attached Files

  4. #4
    Forum Expert Logit's Avatar
    Join Date
    12-23-2012
    Location
    North Carolina
    MS-Off Ver
    Excel 2019 Professional Plus - 2007 Enterprise
    Posts
    6,986

    Re: sick time accrual

    .
    caitline23:

    Hope you don't mind .. I changed around your format slightly :


    A
    B
    C
    D
    E
    F
    G
    H
    I
    J
    K
    L
    1
    NAME John Smith DATE OF HIRE 2016 UNUSED SICK TIME
    5
    2
    July 3, 2016
    USED SICK TIME
    2
    3
    PAY DATE HOURS WORKED SICK TIME ACCRUED TOTAL ACCRUED SICK TIME USED FORMULAS:
    4
    1/1/2017
    30
    1
    1
    1
    E1 : =SUM(D4:D56)-SUM(E4:E56)
    5
    1/8/2017
    40
    1
    1
    E2 : =SUM(E4:E56)
    6
    1/15/2017
    31
    1
    1
    1
    D4 : =IF(C4<>"",C4,"") copied down column
    7
    1/22/2017
    28
    8
    1/29/2017
    40
    1
    1
    9
    2/5/2017
    40
    1
    1
    10
    2/12/2017
    40
    1
    1
    11
    2/19/2017
    56
    1
    1
    12
    2/26/2017
    13
    3/5/2017
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    09-04-2017
    Location
    cape cod, ma
    MS-Off Ver
    10
    Posts
    11

    Re: sick time accrual

    Changing the format is completely fine! Although, I do notice that if the w/e 1/1 only has 26 hours, but w/e 1/8 has 26- so far they worked 52 hours which would be one hour sick time; and then the next week, they would work 26 hours and would earn another sick hour. Most employees here are part-time and do not work a full 40 in one week.

    essentially- for every 40 work week employees, they earn 4 hours in 3 weeks. (30 +10; 30 +10; 30 +10= 4 sick hours)


    Massachusetts law has made this big head ache for us. and we've been doing it with pen and paper since the law was passed.

  6. #6
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,427

    Re: sick time accrual

    Perhaps this proposed solution would meet the guidelines of Massachusetts law.
    The formula for C4 and down is: =INT(SUM(F3,B4)/30)
    The formula for E4 is: =MIN(40,D2+C4-D4)
    The formula for E5 and down is: =MIN(40,E4+C5-D5)
    Note: I am assuming that 40 hours is the maximum an employee may accumulate based on post #1 and the formula for column C in the file attached to post #3
    The formula for F4 and down is: =SUM(F3,B4)-C4*30
    Note: Column F is a helper column which may be moved and/or hidden for aesthetic purposes.
    Let us know if you have any questions.
    Attached Files Attached Files

  7. #7
    Forum Expert Logit's Avatar
    Join Date
    12-23-2012
    Location
    North Carolina
    MS-Off Ver
    Excel 2019 Professional Plus - 2007 Enterprise
    Posts
    6,986

    Re: sick time accrual

    .
    Worked in government for 20 years. Gotta love those "who are so smart" they don't have common sense.

    Trust JeteMc has your answer.

    Cheers !

  8. #8
    Registered User
    Join Date
    09-04-2017
    Location
    cape cod, ma
    MS-Off Ver
    10
    Posts
    11

    Re: sick time accrual

    Ahh! I think this is it! I am not at my desk right now, but I did a little test on excel on my phone. Thank you SO much!

  9. #9
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,427

    Re: sick time accrual

    You're Welcome and thank you for the feedback. If testing ultimately returns positive results, please take a moment to mark the thread as 'Solved' using the thread tools link above your first post. I hope that you have a blessed day.

  10. #10
    Registered User
    Join Date
    09-04-2017
    Location
    cape cod, ma
    MS-Off Ver
    10
    Posts
    11

    Re: sick time accrual

    Okay, so I was just able to check out the spreadsheet you made for me. THANK YOU!

    I do notice though, the max accrual someone can earn is 40 hrs total in a year- When I input the time worked and reach 40 hours of sick time it stops at 40 (great!)- but if I input 8 hours of sick time in a given week, it subtracts the 8 hours, but it keeps allowing to earn the 40 hours throughout the pay weeks. Is there anyway that can be fixed?

    Thank you so much for spending time on helping me, it is SO appreciated!

  11. #11
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,427

    Re: sick time accrual

    If I understand correctly then the following modifications may be all that is needed:
    1) In cell E4 change the formula so that it reads: =MIN(40-D4,D2+C4-D4)
    2) In cells E5 and down change the formula so that it reads: =MIN(40-SUM(D$4:D5),E4+C5-D5)
    Let us know if you have any questions.

  12. #12
    Registered User
    Join Date
    09-04-2017
    Location
    cape cod, ma
    MS-Off Ver
    10
    Posts
    11

    Re: sick time accrual

    Perfect! Thank you!

  13. #13
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,427

    Re: sick time accrual

    You're Welcome. Thank You for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.

  14. #14
    Registered User
    Join Date
    12-17-2018
    Location
    New Jersey
    MS-Off Ver
    new
    Posts
    2

    Re: sick time accrual

    Hi, I actually need the same thing as the above person, however, the modification you gave gives me the err:508
    Thanks for any help!

    Ernie
    Attached Files Attached Files

  15. #15
    Registered User
    Join Date
    12-17-2018
    Location
    New Jersey
    MS-Off Ver
    new
    Posts
    2

    Re: sick time accrual

    Actually I just can;t get it to work. Does anyone have a working copy?
    Ernie

  16. #16
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Office 365
    Posts
    10,316

    Re: sick time accrual

    Hi Ernie,

    Unfortunately your post does not comply with Rule 4 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.
    HTH
    Regards, Jeff

  17. #17
    Registered User
    Join Date
    11-21-2019
    Location
    New York, New York
    MS-Off Ver
    2016
    Posts
    1

    Re: sick time accrual

    I've noticed with this spreadsheet that when an employee uses sick time it doesn't subtract it from the E column (balance). I believe I attached a photo of what I'm talking about.
    Attached Images Attached Images

  18. #18
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,427

    Re: sick time accrual

    Administrative Note:

    Hello picklejar14 and Welcome to Excel 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

    Let us know if you have any questions.

  19. #19
    Registered User
    Join Date
    01-26-2023
    Location
    New York
    MS-Off Ver
    Pro Plus 2021
    Posts
    19

    Re: sick time accrual

    Is there a way to add carry over from a previous year. and still not have exceed 40hrs in the calender year.

  20. #20
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

    Re: sick time accrual

    Administrative Note:

    Welcome to the forum allprovirgil .

    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
    Jeff
    | | |會 |會 |會 |會 | |:| | |會 |會
    Read the rules
    Use code tags to [code]enclose your code![/code]

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Assistance with calculating sick time accrual
    By georgettem in forum Excel General
    Replies: 2
    Last Post: 12-22-2015, 04:18 PM
  2. [SOLVED] Timesheet vacation & sick time accrual
    By Dowjd in forum Excel General
    Replies: 2
    Last Post: 06-28-2015, 10:41 PM
  3. Sick/PTO Accrual Spreadsheet Help
    By alauratag in forum Excel General
    Replies: 3
    Last Post: 03-31-2015, 04:01 PM
  4. Formula for Calculating (Accrual) Vacation Time/ sick time
    By bsayers in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-02-2014, 02:00 AM
  5. Vacation and Sick Leave Accrual formula
    By Dowjd in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 10-01-2013, 10:24 PM
  6. Calculating accrual of sick days
    By wbcsc in forum Excel General
    Replies: 6
    Last Post: 04-13-2010, 02:57 PM
  7. [SOLVED] Vacation/Sick accrual
    By [email protected] in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 02-15-2006, 03:45 PM

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