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

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...

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

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.

In D2:

Formula:

=($A2=$A3)*(NETWORKDAYS($B2,$B3)>2)+($A2<>$A3)

In E2:

Formula:

=IF($D2,SUM($C2:INDEX($C$1:$C2,MAX(INDEX(ROW($D1:$D$2)*IF(ROW()=ROW($2:$2),0,$D1:$D$2),))+1)),0)

Copied downward.

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:

=IF(ROW()=ROW($2:$2),1,$D1+IF(OR($A2<>$A1,AND($A2=$A1,NETWORKDAYS($B1,$B2)>2)),1,0))

In E2:

=SUMIF($D$2:$D2,$D2,$C$2:$C2)

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))

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:

=IF(ROW()=ROW($2:$2),1,IF(OR($K1="-",$K1=MAX($D:$D)),"-",$K1+1))

in L2:

=IF($K2="-","-",SUMIF($D$2:$D$28,$K2,$C$2:$C$28))

Copied downward as far as required.