Count Consecutive Days of Absence Per Staff

[shenggay18]

Can you please help me? What i want to do is indicate a Y or N on the 3rd column, if the date on the 2nd column is consecutive for the staff. On the 4th column, id like to know how many days is the duration of the consecutive dates.

Screenshot 2020-12-24 at 7.10.28 PM.png

[AliGW]

Welcome to the forum.

There are instructions at the top of the page explaining how to attach your sample workbook.

[shenggay18]

Can you please help me? What i want to do is indicate a Y or N on the 3rd column, if the date on the 2nd column is consecutive for the staff. On the 4th column, id like to know how many days is the duration of the consecutive dates.

Attachment 710248

attaching excel file.

[kvsrinivasamurthy]

Consecutive days up to what date. What about weekends and holidays.

[shenggay18]

Consecutive days up to what date. What about weekends and holidays.

continuous consecutive days, regardless of weekends and holidays.

[FlameRetired]

If I understand correctly in C2 and down

Please Login or Register  to view this content.

and in D2

Please Login or Register  to view this content.

[bebo021999]

With H2:H9 is holidays

In C2

Please Login or Register  to view this content.

Note that: if 1-Jan-20 is holiday, Mary has absence in 31-Dec-19 and 2-Jan-20, this day "2-Jan-20" indicates "Y"

D2:

Please Login or Register  to view this content.

[shenggay18]

If I understand correctly in C2 and down

Please Login or Register  to view this content.

and in D2

Please Login or Register  to view this content.

hi, thank you for your reply. the first formula works but the 2nd one does not meet the condition. I want to count the number of days per duration. so for example, 1st absence instance is 5 days, it should be 5 on column D, if there is a 2nd absence instance and it is for 3 days, i should have 3 on column D.

[kvsrinivasamurthy]

In C2 then copy down

=IFERROR(IF(INDEX($B$1:$B1,AGGREGATE(14,6,ROW($A1:$A$2)/($A1:$A$2=$A2),1))=$B2-1,"Y","N"),"N")

In D2 then copy down

=IF(C2="N","",SUMPRODUCT(--(INDEX($A$1:$A2,AGGREGATE(14,6,ROW($A$1:$A2)/(($A$1:$A2=$A2)*($C$1:$C2="N")),1)):$A2=$A2)))

[kvsrinivasamurthy]

If you want display only on the last day of absence .
In D2 then copy down

Please Login or Register  to view this content.

[Glenn Kennedy]

That's neat, Sir. It's not the last value, though. It's the last one, IF they are consecutive down the column, othwerwise it's the first. But a very nice solution!!

[kvsrinivasamurthy]

Dear Glenn Kennedy

I mean the last day of consecutive absence in that period.
Thanks for the comments.

[shenggay18]

AMAZING! Thank you so much!