Calculating a Bradford score from a list of dates.

Need to see what your data looks like. Can you get a raw list of dates per employee and change the employee names to something fake? It doesn't have to be a full file, just enough examples to show the format of your data. In particular, does the data show what the weekend days are for each employee? Or does it vary by week?

I think this can be done with formulas but may require VBA.

The paper clip icon does not work for attachments. Instead, under the text box where you type your reply click the Go Advanced button. On the next screen scroll down and click on Manage Attachments, which will show a pop-up window to Select and Upload a file. Then close the window.

Hi, there are a few options here, but it's not clear how you want to record the results etc... i.e. summary table with "elegant" single cell calc {will be relatively expensive to calculate}, or are happy to use helpers to simplify identify results... we're also assuming your data is always provided as illustrated - i.e. sorted by Associate & Date.

In reference to your variable weekend issue, you could use the NETWORKDAYS.INTL function to determine a "break" with variable weekends -- using your sample file:

Formula:

D2: =IF($A2<>$A1,1,N($D1)+(NETWORKDAYS.INTL($B1,$B2,IFERROR(VLOOKUP($A2,$O:$P,2,0),"0000011"))>2))

copied down



E2: =IF($A2=$A3,"",$D2^2*COUNTIF($A$2:$A2,$A2))

copied down

the above would generate 20 & 15 in E6 & E21 respectively

the reference to O:P is simply to allow you to record non-standard weeks per Associate, to illustrate using example data, enter below into your file:

Formula:

cell O2 enter 1

cell P2 enter '0001011 

{note use of apostrophe prefix}

the above would result in E6 reverting to 5 from 20 - i.e. the result of fact that Thursday is no longer considered a working day for this Associate per P2 string.

hopefully this will be of use to you.

Given the revised data (i.e. all expected days included) a single-cell {Frequency} calc, per Associate, will be simpler -- however I confess I'm pushed for time so, using sample file:

Formula:

D2: =IF($A2<>$A1,1,N(D1)+($C2<>$C1)*$C2)

E2: =IF($A2=$A3,"",($D2^2)*COUNTIFS($A$2:$A2,$A2,$C$2:$C2,TRUE))

copied down

the above would generate your expected results, I think... if you want to make more elegant post back -- alternatively one of the other contributors can provide for you.

Hope that helps.

edit: in answer to your question...

The 1 in O2 is that the employee number? The '0001011 if P2 does that represent Thursday some how?

Yes, the 1 was to denote the ID... the 7 digit string (prefixed with apostrophe) is used to record binary value for Mon-Sun, where a 0 represents a working day
{this allows NETWORKDAYS.INTL to calculate working days between two dates relative to any work week permutation}

the above isn't required given revised data-set.