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

You could consider using NETWORKDAYs to establish days between absences, eg:

Please Login or Register  to view this content.

you can then use that column for your final calc, ie:

Please Login or Register  to view this content.

If you have multiple people to differentiate you can of course use SUMIF etc...or even use a Pivot Table with Calculated Field to generate Bradford Factor ie =Absence^2*'Total Days Absent'

EDIT:
for sake of clarity I have uploaded an attached proof of concept

NOTES:
-- I've assumed XL2007 in the above - if not you would of course need to activate Analysis ToolPak (Networkdays) to use the above

-- I've assumed data is sorted by Date, grouped by Individual

Rennier, if you wish to contact a Member directly with questions of this nature please do so via PM - esp. where threads are old.