How to count consecutive dates for each employee from a listing of absence dates?

Hello,

First of all, I am very excited to have found this site! I've reviewed some threads and the answers/help given are brilliant I'm hoping to seek a bit of your time to help me with a solution that I believe exists, but that I've not had luck figuring out myself or finding a similar question already answered on a forum.

In short, I have a report that contains employee absence data where each calendar date absent is shown with a new row. However, I need to use the data to understand how many occurrences someone was out versus number of days (i.e. if an employee is out Tuesday through Thursday, that would be one chargeable absence; however, the report we are able to run shows it as three absences since the absence spans three days). If someone can please suggest a formula I can use to somehow tell Excel to count consecutive absences for each employee as one absence it would be a huge help.

I inserted my file and I'm inserting a screenshot in hopes that it will make it more clear. I sincerely appreciate your help in advance!!

For example, in this data set below, I'm hoping a formula can be applied that will show Beth had one absence in April (April 4-6th) and one absence in May (May 2nd).

Excel Example - Need to count consecutive dates as 1 occurrence.JPG

Book1.xlsx

Note: I have it sorted by name currently, but can use employee number as well if that would work better for a formula.

Thank you again!!

Hi, welcome to the forum

See if this will get you started...
=IF(AND(A4=A5,B4+1=B5),"",1)
copied indo D4 and dcopied down

Originally Posted by FlameRetired

We are to assume weekends don't break the continuity.......Friday followed by a Monday is still one absence?

But OP shows Saturday and Sundays in Leaves List.

I'll hazard a guess with the attached. I used a helper column J and counted the contiguous periods in column K.

Columns E, F, G and H were there to help me find some kind of pattern.

BTW: the file had some dates that were text.

Edit: Both formulas are array formulas. They must be committed by simultaneously pressing Ctrl + Shift while hitting Enter. Then fill down. You'll know it is entered correctly when you see the {} curly braces around the formula. You don't type these in yourself. Excel does it for you.

Another BTW: That ugly formula in column J can be replaced by this.

Formula:

=IF(SUM(IFERROR(IF(IF($A4=$A4:$A$20,ROW(INDIRECT($C4&":"&MAX($C$4:$C$20))),"")=$C4:$C$20,1,""),""))=1,1,0)

It is array entered also.

hmm good point, FT, didnt think about w/e's

How your output would be? pls attach sample excel sheet

Administrative Note:

Welcome to the forum.

We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

Please see Forum Rule #1 about hijacking and start a new thread for your query.

If you are not familiar with how to start a new thread see the FAQ: How to start a new thread