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