Search code examples
exceldateexcel-formulacount

Excel: Counting illness incidents

I have a table with columns "name", "illness" and "date". From the data, I would like to calculate the number of illness incidents:

  • related dates correspond to one illness incident
  • related dates with a weekend in between also correspond to one illness incident

Attached an example. The column "illness incidents" is what I want to have calculated.

table_illness_incidents

Any idea how to implement this?

I know formulas like datediff, countif, ... but have no idea how to implement this regarding my problem.

Solution

  • enter image description here

    Formula in column C is:

    =IF(SUMPRODUCT(--($A$2:$A$22=A2)*--($B$2:$B$22=WORKDAY(B2;-1)))=0;1;"")

    The formula just check for each date if there is another previous date that it's 1 workday before input day. That way the result 1 would indicate the start of the illness incident.

    If we filter by Homer this would be ypur expected output:

    enter image description here

    Each 1 value just shows where each illness period started.

    UPDATE: This workaround asumes your data got unique names. So, if you got 2 coworkers named Homer those rows will be treated as a single Homer guy