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

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

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.

How your output would be? pls attach sample excel sheet

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:

Please Login or Register  to view this content.

It is array entered also.

At the moment no I can't.

I've attempted some Dynamic Named Range approaches that have not worked so far.

I don't think getting the last row from 20 to 55 (without editing each time this changes) will be a problem. It's coordinating that with the relative row addresses of the first rows in each range (when defining these DNRs) that have me stumped at this time. Those also define the lower boundaries in a contiguous calendar array.

This could take me awhile. Will keep trying.

I am considering some alternative approaches to the original formula. Without an example I can't tell if any of them is going to do what you want. If you upload a desensitized example of the 55 row scenario it would be helpful.

Excel "hangs" and then tells me it can't load the file because it's corrupt.

Edit:

I remember this one from a few months ago.

I was eventually able to download the file. I copied and pasted data into the original one with these reworked formulas:
Array-enter this one in F4 and fill down.

=IF(COUNTIF($A$4:$A$500,$A4)=1,1,IF(SUM(IFERROR(IF(ROW(INDIRECT($B4&":"&MAX($B4:$B5)))=$B4:$B5,1,0),0))=1,1,0))

The rest do not need to be array entered.

In G4 this one.

=SUMIF($A$4:$A$500,$A4,$F$4:$F4)

I added a running totals column in column H. No other formulas depend upon it so delete it if you don’t want it. That formula in H4 is this one.

=SUMIF($A$4:$A4,$A4,$F$4:$F4)

For the summary table in J:K this one in K4 and filled down.

=SUMIF($A$4:$A$500,$J4,$F$4:$F$500)

Chronic need for editing shouldn’t be a problem if the last row is set high enough to accommodate the most data you will anticipate plus a few. I used 500. Avoid the temptation to use whole columns. That puts unnecessary load on your system.

The file is attached.

Does this do what you want?

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