# Calculating a Bradford score from a list of dates.

1. ## Calculating a Bradford score from a list of dates.

Hi,
I'm getting a list of dates of absence output from a time & attendance system. They can't, or won't, calculate a Bradford score for me. Can anyone help me do it in Excel.

A Bradford score is relatively simple it's just : Sē x D = B where S is spells of absence, D is total days of absence and B is the Bradford score.

e.g.
Absence
1st Jan 2018
2nd Jan 2018
3rd Jan 2018
1st Feb 2018
10th Feb 2018

2 spells of absence, 5 days of absence would give a Bradford score of 20.

whereas

Absence from 1st Jan 2018 to 19th Jan 2018 inclusive

1 spell of absence, 19 day of absence would give a Bradford score of 19.

There is an added complication of weekends not being counted. An absence from Thursday to Tuesday is only 1 spell and only 4 days. The final complication, although I could probably live without this, is that the weekend might be Sat & Sun for one employee but Sun & Mon for another.

As I said above I can get a raw list of dates per employee and I like to use Excel to calculate Bradford scores for each employee.

Regards,
Jim.

2. ## Re: 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.

3. ## Re: Calculating a Bradford score from a list of dates.

Hi 6StringJazzer,

I have attached a raw csv file of what I expect to get.

The first 2 columns are all I will get, I have added a couple of comment columns to try and explain what I need.

Regards,
Jim.

4. ## Re: Calculating a Bradford score from a list of dates.

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:
`Please Login or Register  to view this content.`

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:
`Please Login or Register  to view this content.`

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.

5. ## Re: Calculating a Bradford score from a list of dates.

Hi,
Thanks for your help. That works although I could do with an explanation for what some things mean.
The 1 in O2 is that the employee number? The '0001011 if P2 does that represent Thursday some how?

I'm sorry but I've been thinking about this and it's not going to work in the way I've given you!

I'm actually going to need a list of the dates each employee is scheduled to work and then an indicator to say whether they worked or were absent.

So it'll be a csv file as per the attached. I just have to see if I can get a file in this format now I'm confident I can.

You can assume that the csv file will be sorted by employee number then date order.

Thanks again for all your help.

Regards,
Jim.

6. ## Re: Calculating a Bradford score from a list of dates.

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:
`Please Login or Register  to view this content.`

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.

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.

7. ## Re: Calculating a Bradford score from a list of dates.

Hi,
That works great thanks. Now I just have to try and get the data in the correct format.

The '0001011 thing is useful to know also.

Regards,
Jim.

There are currently 1 users browsing this thread. (0 members and 1 guests)