+ Reply to Thread
Results 1 to 7 of 7

Calculating a Bradford score from a list of dates.

  1. #1
    Registered User
    Join Date
    10-25-2018
    Location
    N. Ireland
    MS-Off Ver
    Office 365
    Posts
    4

    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. #2
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    365
    Posts
    17,676

    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.
    Jeff
    | | |·| |·| |·| |·| | |:| | |·| |·|
    Read the rules
    Use code tags to [code]enclose your code![/code]

  3. #3
    Registered User
    Join Date
    10-25-2018
    Location
    N. Ireland
    MS-Off Ver
    Office 365
    Posts
    4

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

    Hi 6StringJazzer,

    Thanks for your reply.

    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.
    Attached Files Attached Files

  4. #4
    Forum Expert XLent's Avatar
    Join Date
    10-13-2010
    Location
    Essex, UK
    MS-Off Ver
    various
    Posts
    2,102

    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: copy to clipboard
    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: copy to clipboard
    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.
    Last edited by XLent; 10-26-2018 at 05:39 AM.

  5. #5
    Registered User
    Join Date
    10-25-2018
    Location
    N. Ireland
    MS-Off Ver
    Office 365
    Posts
    4

    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.
    Attached Files Attached Files

  6. #6
    Forum Expert XLent's Avatar
    Join Date
    10-13-2010
    Location
    Essex, UK
    MS-Off Ver
    various
    Posts
    2,102

    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: copy to clipboard
    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.

    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.
    Last edited by XLent; 10-29-2018 at 10:15 AM.

  7. #7
    Registered User
    Join Date
    10-25-2018
    Location
    N. Ireland
    MS-Off Ver
    Office 365
    Posts
    4

    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.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. IF formula help on calculating a score
    By liqt in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 02-22-2018, 09:35 AM
  2. [SOLVED] Calculating Differences between dates for a Waiting List Calculation
    By richiev86 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 02-14-2014, 12:42 PM
  3. Calculating bradford factors (array formula?)
    By demonfly100 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 12-07-2012, 09:28 AM
  4. Replies: 2
    Last Post: 03-20-2012, 06:30 PM
  5. Register with Bradford Score
    By mattdrake in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 06-12-2009, 03:01 PM
  6. Rank list from top score to low score
    By ladnermd in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 02-24-2008, 05:05 PM
  7. Calculating the closest score
    By AussieM8 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 04-24-2007, 08:21 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1