Pro rata absence data for various shift patterns

[CatMadJulie]

We currently have 3 shifts:
Dayshift (Monday to Friday, 38.5 hours total)
3-Day Shift (3 days per week, 36 hours total)
Nightshift (4 nights per week, 38.5 hours total)

From this site I have found the formula I wanted to use to rate people's absence using the Bradford Factor*. However, to make it fair, I need to adjust the scores to take into account a person's shift pattern.

E.g. Two shifts lost in one week for a Dayshift person is 2/5, for a Nightshift it is 2/4 and for a 3-Day Shift person it's 2/3 of their normal working week.

So if a Dayshift person goes off sick for 2 days, it is less time lost from their normal working week than if a Nightshift person went off for 2 nights.
How can I make adjustments to their absence figures to take this into account so that the scores are fair across all shifts?

Any help would be much appreciated.

Thanks.

* The Bradford Factor is a formula which multiplies the number of absences by itself and then by the total number of days lost. So if A = Absence and D = Days Lost, the formula would be: A x A x D = Bradford Factor Score

 

[BrianB]

I am not a mathematician but would think that the matter depends on the number of *hours* lost, so I would multiply the Bradford Factor per day :-
Day 7.70 hours
3 Day 12.0 hours
Night 9.625 hours

 

[nbrcrunch]

Julie

Sometimes accounting questions like this are dangerous to post here. What you ask is a math question, not Excel. Our lack of knowledge of the mindset of your company might provide you with a completely "logical" solution but not one that your accounting department would accept. I suggest you pose your math question to your manager(s) and see how they want to handle this.

 

[BrianB]

not one that your accounting department would accept

I work in the Finance Department of a large company.
The correct answer from one of my colleagues would be
"What do you want it to be ?"

 

[CatMadJulie]

Thanks you two. After your advice and further consultation with a couple of colleagues, I worked out what was needed - multiply the Bradford Factor result by 1.25 for Nightshift or 1.66 for 3-Day shift. I've now just managed to fudge together a VLOOKUP table and added a new column to run this calculation for me. Result!

Thanks again for your help.

 

[rachelhhall]

Hi Julie,

I am fairly new to the concept of the Bradford factor and am trying to model it on Excel for a small medical practice with 10 staff. I wondered if you could point me in the right direction to get started as I am not an advanced Excel user. If you could just give me a brief understanding of how you used pivot tables to solve this I would be very grateful.

Many thanks

Rachel

rachelhhall@aol.com