Bradford Factor - How to count number of instances while using words

[vgr]

Hi,

I need to figure out a way to count the number of times a person has taken leaves and count the number of instances.

Bradford example.xlsx

Please do take a look at the file and tell me is there is a way in which this can be solved.
Thanks

[Glenn Kennedy]

There WILL be a nice way to do this. In the meantime, here's an ugly way...

You will run into a couple of other issues - what happens at month ends - if sick leave runs over the end of one month and the start of another. You might need to think a bit more about the basic structure of your data to get round this.

[vgr]

Neat trick Glenn.... Thanks for your prompt response...
I sure didn't think this through... Wow month end... I initially assumed that the employees are going to be constant but well as it turns out, we do hire a lot of temps and the employee list changes every month. Another crude method would possibly be to use a vlookup for an entire years worth of attendance from individual excel files for each month[That's how the data was collected last year - one excel file per month]. Any ideas on how one could possibly collate the data in an easier way?
I wish I had collected the data as line items.

[martindwilson]

there is no NICE way to calculate or use the bradford factor

[vgr]

there is no NICE way to calculate or use the bradford factor

No nice way to use the factor? why so? The logic seems sound. Short frequent leaves do a lot more than leaves taken at a stretch in a production setting.

[Glenn Kennedy]

Martin,

You're right... It's a bit of a "blunt instrument" that, in my experience, tends to cause distress to those with genuine problems.

[vgr]

Martin,

You're right... It's a bit of a "blunt instrument" that, in my experience, tends to cause distress to those with genuine problems.

@Martin, Glenn:
Is there a better alternative to the Bradford Factor?
It's just that absenteeism is really tough to control. The disruptions it causes lead to major changes in the planning and scheduling......

[Glenn Kennedy]

If I can figure out how... I'll send you a PM.

[Glenn Kennedy]

Try this - it runs over a year & takes both weekends and holidays into account. I'm offline now (pub time...). Back tomorrow.

[TMS]

There's nothing wrong with the Bradford Factor if it is used as a measurement tool and not as a blunt instrument to hit staff and managers with. It highlights patterns of absenteeism which can then be discussed with the member of staff. It may be they're not very good at getting up to come to work on a Monday after a weekend on the beer but, equally, there could be some underlying health issue. Hopefully, if it is the latter, both the manager and HR would be aware of the issue and it would be taken into account.

Unfortunately, in my experience, it does become a blunt instrument ... a measure, which becomes a target, which must then be reduced. Otherwise, it then reflects on the performance and bonus of the individual and all the managers in the management chain. And, as consequence, it goes the way of all measures, targets and statistics ... a way of working around it must be found ... usually termed cheating. OR, a performance improvement plan is put in place to manage the member of staff's absenteeism, putting both the individual and the manager under pressure and causing stress.

So, all that said, I have to agree with Martin. There's no NICE way of measuring the Bradford Factor. It WILL turn nasty.

Regards, TMS

[martindwilson]

.................................................................deleted

[Xx7]

This should be done via programming. This is an AHK script that does it, however the logic will be identical to VBA.