+ Reply to Thread
Results 1 to 10 of 10

SETUP - Sickness recording using Bradford Factor for 12 month rolling period

  1. #1
    Registered User
    Join Date
    09-15-2015
    Location
    England
    MS-Off Ver
    2007
    Posts
    19

    SETUP - Sickness recording using Bradford Factor for 12 month rolling period

    Hi,

    I am trying to set up a spreadsheet which will calculate the rolling sickness absence for the last 12 months for 133 staff.
    I don't want to 'reinvent the wheel' so wondered if anyone was willing to share ideas? Or systems that work for you?

    Very much appreciated!

    Thanks

  2. #2
    Forum Expert NeedForExcel's Avatar
    Join Date
    03-16-2013
    Location
    Pune, India
    MS-Off Ver
    Excel 2016:2019
    Posts
    3,865

    Re: SETUP - Sickness recording using Bradford Factor for 12 month rolling period

    How do you maintain the data for these 133 people?
    Cheers!
    Deep Dave

  3. #3
    Registered User
    Join Date
    09-15-2015
    Location
    England
    MS-Off Ver
    2007
    Posts
    19

    Re: SETUP - Sickness recording using Bradford Factor for 12 month rolling period

    Hi Dave,

    The information is currently input into individual tabs within a 'Employee Record' spreadsheet....... This is ran from 1st Oct to 30th Sep so would need to merge together data from across 2 separate spreadsheets first.....

  4. #4
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    17,865

    Re: SETUP - Sickness recording using Bradford Factor for 12 month rolling period

    Look here for sample Excel calculator...



    https://www.gov.im/lib/docs/hr/bradf...calculator.xls

  5. #5
    Registered User
    Join Date
    09-15-2015
    Location
    England
    MS-Off Ver
    2007
    Posts
    19

    Re: SETUP - Sickness recording using Bradford Factor for 12 month rolling period

    Thank you John,

    I understand the calculation behind the BF. I am trying to create a 'Master' spreadsheet that you have each individual employee on and pull through their BF score.

  6. #6
    Forum Expert
    Join Date
    10-09-2014
    Location
    Newcastle, England
    MS-Off Ver
    2003 & 2013
    Posts
    1,829

    Re: SETUP - Sickness recording using Bradford Factor for 12 month rolling period

    It all depends on how your spreadsheet works really, the Bradford calculation is straight forward and only involves two factors (number of sick periods and total days), the issue comes in how you get that info to the calc. For instance if your sheet shows all the days and you're looking to pull the items marked "Sick" then thats harder than just a basic list of sick absences.

    Ive attached a simple sheet showing the most basic example.
    Attached Files Attached Files
    If someone has helped you then please add to their Reputation

  7. #7
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    17,865

    Re: SETUP - Sickness recording using Bradford Factor for 12 month rolling period

    Can you give an example of current files?

    If you had a sheet per calendar month (with all employees) then it is easy to sum the number of sick days across 12 sheets.

    Equally you could keep a monthly total on each monthly sheet of total days sick and total periods of sickness: again you can then sum across worksheets.

    I doubt if there is standard template as it often depends on who, and how, an organisation, arranges the data collection and its processing: the calculations are straight forward (as you know better than me!

    Keeping running totals (year-to-date) would allow any "warning" triggers to be highlighted.

    In the end, keep it simply!

  8. #8
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    17,865

    Re: SETUP - Sickness recording using Bradford Factor for 12 month rolling period

    Attached is sample workbook using monthly records with a VBA macro to count the Sickness days and the number of sickness periods. It currently takes no account of holidays i.e. someone sick over a holiday weekend.

    The YTD sheet simply sums over the selected worksheets.
    Attached Files Attached Files
    Last edited by JohnTopley; 09-15-2015 at 08:41 PM.

  9. #9
    Registered User
    Join Date
    09-15-2015
    Location
    England
    MS-Off Ver
    2007
    Posts
    19

    Re: SETUP - Sickness recording using Bradford Factor for 12 month rolling period

    Thank you everyone!

    Not quite there yet but working on it!!!

  10. #10
    Registered User
    Join Date
    09-22-2015
    Location
    London
    MS-Off Ver
    7
    Posts
    1

    Re: SETUP - Sickness recording using Bradford Factor for 12 month rolling period

    With that number of employees you are better off using an Absence Management solution.

    In order to properly calculate the Bradford Factor you have to take into account any holidays - public or annual and also any part-time working. If an absence starts before a holiday or before a day that is not normally worked and is still continuing on their next working day, that should only be calculated as one period. The chance of errors when doing the calculation using Excel is too high.

    We've only got 35 employees and use Teamseer. Each manager gets a report each Monday detailing any absences over previous 4 weeks, quarter and year for their department, and any employee can see their own Bradford Factor 12-month score. That enabled employees to buy into the process and reduced the Monday absences that the system highlighted. One thing the system also highlighted was that the employees in our Aberdeen office have different bank holidays to those in England.

+ 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. Bradford Factor
    By ryho33 in forum Excel General
    Replies: 25
    Last Post: 06-26-2019, 04:34 AM
  2. Calculating Total Sickness in a rolling 12 month Period
    By china in forum Excel Formulas & Functions
    Replies: 13
    Last Post: 09-08-2015, 08:04 AM
  3. Replies: 10
    Last Post: 01-09-2014, 07:04 PM
  4. Bradford Factor
    By francesjan48 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 05-03-2012, 09:02 AM
  5. requests Bradford Factor
    By martindwilson in forum The Water Cooler
    Replies: 11
    Last Post: 11-04-2010, 07:53 PM
  6. bradford factor
    By Tjenkins in forum Excel General
    Replies: 8
    Last Post: 10-29-2010, 06:38 AM
  7. Bradford factor period calculation
    By dragsapphire in forum Excel General
    Replies: 5
    Last Post: 08-05-2010, 04:17 AM

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