+ Reply to Thread
Results 1 to 13 of 13

Count # Sick Day Occurrences based on dates and the Duration of each occurrence in hours

  1. #1
    Registered User
    Join Date
    07-19-2012
    Location
    Canada
    MS-Off Ver
    Excel 2010
    Posts
    7

    Count # Sick Day Occurrences based on dates and the Duration of each occurrence in hours

    Hi, this is my first question to the forum. I really need help. See the attached sample spreadsheet. I need to do 2 things:

    #1:
    I need to to count the number of sick occurences per employee number.
    One sick occurrence equals CONSECUTIVE DAYS OFF SICK by date. One sick occurrence could be separated by 2 days (a weekend) off work, usually a Friday and Saturday . So one occurrence could be a few hours, 1 day or many days. If an employee is sick one day, back to work the next day and then off sick for 4 hours on the 3rd day, this would be 2 sick occurrences in the 3 day period. Occurrence 1 is 7.5 hours and occurrence 2 is 4 hours.

    I have tried this formula to get a count of occurrences per employee #:
    =IF(AND(A3=A2,(OR(B3=B2+1,B3=B2+3))),0,1)
    There is probably a better way for the above but I don't know what it is.

    #2 I need to count the duration in #hours of each occurrence for each employee.

    Help please. Thanks.
    Attached Files Attached Files

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

    Re: Count # Sick Day Occurrences based on dates and the Duration of each occurrence in hou

    Hi Elizabeth and welcome to the forum,

    I have an answer for you. See the attached. You will need to sort your table by Sick Date and then by EE IDN numbers in that order. Then add two helper columns. The first is my Workday column where I give the date of the next workday for that date. This deals with weekends. You can also (somehow) deal with holidays using workdays. Then in my Incidents formula I give a 1 or zero if they are consecutive and the same IDN numbers. A pivot table is used to sum the total hours and incidents per employee.

    I think that is what you want. You will need to sort before using the Incident formula as it deals with the next row down...
    Attached Files Attached Files
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  3. #3
    Registered User
    Join Date
    07-19-2012
    Location
    Canada
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: Count # Sick Day Occurrences based on dates and the Duration of each occurrence in hou

    Thanks I will take a detailed look and check the one questionable outcome.

  4. #4
    Registered User
    Join Date
    07-19-2012
    Location
    Canada
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: Count # Sick Day Occurrences based on dates and the Duration of each occurrence in hou

    Ok, that looks pretty good, but how do I then get the # of hours for EACH INCIDENT that one employee has. So for #99544, there are 3 occurrences of 15 hours, 7.5 hours and 22.5 hours. I need those each automatically calculated. Do I do that in a pivot?

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

    Re: Count # Sick Day Occurrences based on dates and the Duration of each occurrence in hou

    OK - try this pivot table with a new helper column in the data. see attached. I hope this does it for you.
    Attached Files Attached Files

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

    Re: Count # Sick Day Occurrences based on dates and the Duration of each occurrence in hou

    Hi Elizabeth,

    I could give a correct answer to this problem as the one above was WRONG!!! Thanks for being polite and suggesting I knew what I was doing. I had my bottom/up and Top/Down mixed up and my head was up my ....

    Find the attached with a much better answer. I believe this new one works. Please DON'T use the one above.

    The secret to solving this problem is in about 3 places. First is the Workday() function which will display a number of workdays in the past or future. I created a helper row with the date of the previous workday using Workday(Sick Date, -1). THE DATA NEEDS TO BE SORTED FIRST BY DATE, OLD TO NEW AND THEN BY EMPLOYEE. Then a helper column can be calculated to see if this was a NEW sick incident or the SAME as the workday before it. Finally I used a COUNTIFS() function to see which occurrences was involved and a Pivot Table of this shows FINALLY the correct results.

    Sorry I gave the wrong answer in the above.
    Attached Files Attached Files
    Last edited by MarvinP; 07-21-2012 at 11:50 AM.

  7. #7
    Registered User
    Join Date
    07-19-2012
    Location
    Canada
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: Count # Sick Day Occurrences based on dates and the Duration of each occurrence in hou

    Ok, thanks, will get my head around this newer solution - I was just looking at the first one and quite like the identifying number for each sick day incident regardless of EE IDN. It is helpful in the pivots as I will need to look at sick INCIDENTS under and over a certain duration regardless of the EE IDN. So I will add that feature in once I get my head around your newest solution. Just need a little time to get my brain to catch up to yours!

  8. #8
    Registered User
    Join Date
    07-16-2012
    Location
    Newcastle upon Tyne
    MS-Off Ver
    Excel 2010
    Posts
    69

    Re: Count # Sick Day Occurrences based on dates and the Duration of each occurrence in hou

    In D2:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    In E2:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Copied downward.
    Steve D. a.k.a. Stephen Dunn

  9. #9
    Registered User
    Join Date
    07-19-2012
    Location
    Canada
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: Count # Sick Day Occurrences based on dates and the Duration of each occurrence in hou

    Hi OK, thanks for this last response. I am giving it a try and it works but I really don't understand it and I would like to frankly! MarvinP's idea of having a unique identifying number for each occurrence regardless of EE IDN is an idea I like. I am trying to make that work using your solution but haven't been able to quite get it right. Any pointers for me?

  10. #10
    Registered User
    Join Date
    07-16-2012
    Location
    Newcastle upon Tyne
    MS-Off Ver
    Excel 2010
    Posts
    69

    Re: Count # Sick Day Occurrences based on dates and the Duration of each occurrence in hou

    Hi Elizabeth, okay, these two should give you your incident numbers as you want them, as well as a running total of hours in each incident, and have the bonus of being easier to understand:
    In D2:
    Please Login or Register  to view this content.
    In E2:
    Please Login or Register  to view this content.
    Copied downward.

    Does that help?

    ---------- Post added at 08:15 AM ---------- Previous post was at 07:58 AM ----------

    As an aside, this might be useful in your "Help1" column:
    =TEXT($B2,"dddd")
    and this is how I would normally have used the first formula in post #10:
    =IF(ROW()=ROW($2:$2),1,$D1+($A2<>$A1)+($A2=$A1)*(NETWORKDAYS($B1,$B2)>2))

    ---------- Post added at 08:18 AM ---------- Previous post was at 08:15 AM ----------

    As an aside, this might be useful in your "Help1" column:
    =TEXT($B2,"dddd")
    and this is how I would normally have used the first formula in post #10:
    =IF(ROW()=ROW($2:$2),1,$D1+($A2<>$A1)+($A2=$A1)*(NETWORKDAYS($B1,$B2)>2))

  11. #11
    Registered User
    Join Date
    07-19-2012
    Location
    Canada
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: Count # Sick Day Occurrences based on dates and the Duration of each occurrence in hou

    Hi, thanks, still catching up with you! I used the new suggestion and pivoted on the unique incident number in 2 different ways. Can you take a look at let me know which is safer to use, or if there another better way? Both pivots give me the correct total hours per occurrence but one pivot gives an incorrect overall total so perhaps I have it but you might have an even better suggestion for me! I have about 40000 records I will be using this on so I need to be sure the pivot by unique occurrence will work correctly. Thanks. See attached.
    Attached Files Attached Files

  12. #12
    Registered User
    Join Date
    07-16-2012
    Location
    Newcastle upon Tyne
    MS-Off Ver
    Excel 2010
    Posts
    69

    Re: Count # Sick Day Occurrences based on dates and the Duration of each occurrence in hou

    The second pivot table (Sum of Sick Hours on Sick Date) is definitely the one to use.

    An alternative using formulae, so that you don't have to refresh it manually, would be:
    in K2:
    Please Login or Register  to view this content.
    in L2:
    Please Login or Register  to view this content.
    Copied downward as far as required.

  13. #13
    Registered User
    Join Date
    07-19-2012
    Location
    Canada
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: Count # Sick Day Occurrences based on dates and the Duration of each occurrence in hou

    Thanks, I have it tested in my sample so now I will try it out on my real data and see how it goes.

+ 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