Counting Consecutive Dates As A Single Occasion

Hi all,

I'm putting together a document for the HR department regarding sick leave in the office and they want me to include something called the Bradford Factor which is calculated thus: Occasions x Occasions x Days.

I'm using data downloaded from a database which shows each date each person was away and for what reason. The problem I have in calculating the Bradford Factor is that I can't differentiate between the different occasions someone is away.

What I'm trying to do is to get Excel to look at the dates and, if they are consecutive (Fri - Mon inclusive is also counted as consecutive) I need Excel to tell me that this is one occasion. It then needs to count each seperate occasion.

I've attached an example with each different occasion colour coded. In the example there a five occasions. Thus the Bradford Factor would be 187.5 (5*5*7.5).

TIA,

SamuelT