Counting Consecutive Dates As A Single Occasion

[SamuelT]

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

[DonkeyOte]

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

E1: Absence
E2: =--((NETWORKDAYS(SUM(C1)*(A1=A2),C2,holidays)-1)>1)
copied down
(red section being optional - referring to public holidays)

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

=SUM(E2:E9)^2*SUM(D2:D9)

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

[SamuelT]

That's perfect. Thanks so much DonkeyOte!

[Rennier]

Hi SamuelT!

I don't know if you are still at this address but here's trying.

Could you please forward me your spreadsheet and maybe just an explanation on how you use/d it? I have been tasked to look at absenteeism and using the Bradford Index although we are looking at a period of 26 weeks in which the absenteeism takes place and it seems what you did will help me a great deal.

Thanking you in advance,
Rennier
South Africa

[DonkeyOte]

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