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

1. ## 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. ## Re: SETUP - Sickness recording using Bradford Factor for 12 month rolling period

How do you maintain the data for these 133 people?

3. ## 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. ## Re: SETUP - Sickness recording using Bradford Factor for 12 month rolling period

Look here for sample Excel calculator...

5. ## 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. ## 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.

7. ## 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. ## 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.

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

Thank you everyone!

Not quite there yet but working on it!!!

10. ## 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.

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

#### 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