+ Reply to Thread + Post New Thread
Results 1 to 8 of 8

Rolling Sickness

  1. #1
    Registered User
    Join Date
    01-04-2017
    Location
    Sheffield England
    MS-Off Ver
    MS Office 2007
    Posts
    4

    Rolling Sickness

    Has anyone created a twelve month rolling sickness spreadsheet that would be prepared to share?

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

    Re: Rolling Sickness

    Hi, welcome to the forum

    I guess if anyone has actually done this, they would know what you mean For the rest of us, I think we need lots more detail on what you are trying to do, what does your data look like, what do you expect, etc
    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 some-1 helped you

    Regards
    Ford

  3. #3
    Registered User
    Join Date
    01-04-2017
    Location
    Sheffield England
    MS-Off Ver
    MS Office 2007
    Posts
    4

    Re: Rolling Sickness

    I saw a similar question in this forum although it was some time ago so thought I would join and try and get the spreadsheet but its obviously not as easy as that. I need to establish an individuals absence from a certain date to current sickness absence to check when they hit the 4 weeks date as the employee would not then receive firm's sick pay. For example an employee has gone on long term sick on 16th Dec 2016 so I looked back at 2016 and 2015 attendance records to see when the 4 weeks expired. The employee had odd days off in 2016 (nothing in 2015) and will hit the 4 weeks date at the end of January. This is simple to work out by just looking at the attendance records. However if the employee returns and then goes off again for example on 1st March 2017 I have to look at the same data but the date changes from the 16th Dec working back to the 1st March working back and this could happen again with further absences. Not sure if this explains what I am trying to do or not.

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

    Re: Rolling Sickness

    Perhaps this will get you started?
    A
    B
    C
    D
    1
    Date
    2
    12/1/2016
    x
    11/7/2016
    8
    3
    12/3/2016
    4
    12/5/2016
    x
    5
    12/7/2016
    6
    12/9/2016
    7
    12/11/2016
    x
    8
    12/13/2016
    9
    12/15/2016
    10
    12/17/2016
    11
    12/19/2016
    x
    12
    12/21/2016
    13
    12/23/2016
    14
    12/25/2016
    15
    12/27/2016
    x
    16
    12/29/2016
    x
    17
    12/31/2016
    x
    18
    1/2/2017
    x
    19
    1/4/2017
    20
    1/6/2017
    21
    1/8/2017
    x
    22
    1/10/2017
    23
    1/12/2017
    x
    24
    1/14/2017
    x
    25
    1/16/2017
    x
    26
    1/18/2017
    27
    1/20/2017
    28
    1/22/2017
    x
    29
    1/24/2017
    30
    1/26/2017
    x
    31
    1/28/2017
    x
    32
    1/30/2017
    x

    C2=TODAY()-60
    this would be your start date. To keep the sample small, I just -60 from TODAY(), but if you wanted to go back a year, that would be
    =EDATE(TODAY(),-12)

    Then to pull the data...
    D2=COUNTIFS($B$2:$B$32,"x",$A$2:$A$32,"<="&TODAY(),$A$2:$A$32,">="&$C$2)

    Let me know how you make out?

  5. #5
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    Excel 2010 (Win 7 - Work) & 2016 (Win 10 - Home)
    Posts
    5,187

    Re: Rolling Sickness

    Quote Originally Posted by OxJ View Post
    Has anyone created a twelve month rolling sickness spreadsheet that would be prepared to share?
    It's not a condition I'd heard of until today ...

    Sorry - couldn't resist!

    Perhaps if you attach here the spreadsheet you downloaded and explain how you need to adapt it.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    If anyone has helped you towards solving a problem, then thank them by clicking on their reputation star bottom left.

  6. #6
    Forum Guru newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,294

    Re: Rolling Sickness

    Here is an example that you can play with. I have entered the dates to 31/12/2020 in column A and have entered a 1 beside each date that represents a sick day off. I entered 1/1/2015 to start with so that the formula had a year back to start with. This formula is entered in C3 and filled down the column. It calculates the time taken within a 1 year period on a "rolling" basis.
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    This formula starts off calculating the start date to be considered in column A ($A$2:$A$1829>=EDATE(A3,-12). The end date is the date that you are looking at. For example if you were looking at Jan 2 2017 the starting date would have been calculated by the above formula and the value in column C is the total for the rolling year to that date.
    The end date is calculated with ($A$2:$A$1829<=A3). Both of these dates are then multiplied by the values in the corresponding cells in column C. All of this within the SUMPRODUCT function will give a total for the time period.
    Attached Files Attached Files
    <---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.
    Ron W

  7. #7
    Registered User
    Join Date
    01-04-2017
    Location
    Sheffield England
    MS-Off Ver
    MS Office 2007
    Posts
    4

    Re: Rolling Sickness

    Thank you for your help. I've received another spreadsheet so will try them both out and see which fits my requirements. I am self taught in excel so I need to get my head around some of the formula's.

  8. #8
    Registered User
    Join Date
    01-04-2017
    Location
    Sheffield England
    MS-Off Ver
    MS Office 2007
    Posts
    4

    Re: Rolling Sickness

    Like you I am a self taught enthusiast of excel and always learning. I have received two examples so need to get my head around the formula's and see which will be suitable.

+ Reply to Thread + Post New 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