Count Consecutive Dates

[Sasquatch2014]

A1= Name
B1= Date

I am tracking attendance and one of the rules in the policy is consecutive days absent counts as 1 occurrence.

Column A Column B
Dave Jan. 2, 2016
Dave Jan. 3, 2016
Dave April 1, 2016
Dave April 5, 2016

I would like the count of occurrences, from this example it would be 3 since Jan. 2 and Jan. 3 are counted as 1.

[Glenn Kennedy]

I suspect that this is more complicated than you are currently stating.

What is the effect of weekends and public holidays? Will Fred's absences be in the same column, all mixed up with Dave's?

Please attach a sample workbook. Make sure there is enough data to demonstrate your need. Make sure your desired results are shown, mock them up manually if necessary. Remember to remove ALL confidential information first!!!

However, nothing on this Forum works quite as well as you might expect. The attachment icon doesn't work!! (Helpful, isn't it!!). Instead, just before you submit your post, click on GO ADVANCED (near the bottom) and then scroll down to Manage Attachments to open the upload window. The relevant instructions are at the top of that screen.

[JeteMc]

This solutions employs a helper column (C) which may be hidden for aesthetic purposes. The formula to populate the helper column is:

Please Login or Register  to view this content.

The formula that will count the number of absence occurrences is:

Please Login or Register  to view this content.

Let me know if you have any questions.

[Sasquatch2014]

@Glenn,

I was able to attach my file and you are correct, this is slightly more complicated.

I added more data and my intended results in columns D and E.

if there's a way to include holidays and weekends that would be ideal. For example, an occurrence on Friday and another occurrence on Monday still equals 1 (assuming this employee has Saturday and Sunday off days)

For company holidays, I could probably enter those on my list for every employee if it's not possible creating a formula for it.

[JeteMc]

I modified the formula that counts the number of absences to take into consideration of the employees name:

Please Login or Register  to view this content.

I assume from your comment that some employees work weekends, as in Dave and Steve, which would complicate the blanket inclusion of the workday function.
Let me know if you have any questions.

[Sasquatch2014]

@jetemc, when I enter the formula I get large negative numbers. Can you check the formula again?

[Sasquatch2014]

was able to make the formula work, but this formula does not combine consecutive days.

[JeteMc]

Here is the file with the formulas applied.
Let me know if you have any questions.

[Sasquatch2014]

I think the issue with this formula is I have to have names and dates sorted correctly all the time. For example, I will most likely have multiple employees calling off sick on the same day, and the formula in column D doesn't take that into account.

[JeteMc]

One possible 'fix' might be to create an Excel table that could be sorted either by date to enter new data about absences, or by name to display the correct number of absence occurrences per employee.
Let me know if you have any questions.

[TudyBTH]

Hi,
I propose an adaptation of the JeteMc`s solution, that takes into account holidays and weekends
for helper column (C) use this

Please Login or Register  to view this content.

where H2: H3 is the list of holidays

For List of unique names
use this array formula (in E2 and drag down)

Please Login or Register  to view this content.

and for "Count of occurrences" column

Please Login or Register  to view this content.

see attached

[FlameRetired]

Another way.

Helper formula in column C.

Please Login or Register  to view this content.

In column E

Please Login or Register  to view this content.

I put in some more dates (contiguous across weekends). It seems to work at my end.