Forum Discussion
Excel: Adding staff absences on a rolling 365 days. Plus recording Bradford Factor scores
So I have to work out my staff's sickness days and calculate their Bradford Factor score. However, this is done on a rolling 365 days from the day I work this out.
I've tried using "A" for absence and I tried using "1" as well. Both seem to have their merits but not a complete picture.
I also need to work out the Bradford factor score so I have to work out, not just the number of days sick (in the last 365 days) but also the number of occurrences as well.
The table structure is column A date, column B staff 1, column C staff 2 and so on. I can work out counting the number of sick days is fine. But it's figuring out the <="&TODAY() and >"&TODAY()-365 formulas that works out the number of sick occurrences as well.
To add another spanner to the works, out of all those that have been sick, I need to separate those that are covid related and not add them to the Bradford Factor score.
In my mind for the report/separate sheet, I'll have e.g. from today:
- the number of absences a staff has taken for a rolling 365 days
- out of those, how many days is covid
- how many occurrences overall
- work out the Bradford factor score which is the occurrences x2 x number of actual days off (minus covid related)
All help is appreciated.
6 Replies
- there is a lot here and I'm unclear if you need help with a formula or how to structure the data. For the formula it would appear something like =COUNTIF or COUNTIFA should work. BTW if you want 12 months instead of 365 days you can use EDATE(TODAY(), -12)
As for structure that is more intriguing because it sounds like you need a lot of 'continuous' data or do you just need everything relative to 'Today'. If it is only relative to 'Today' that is much easier as you can either insert a few lines under the header or in a new table copy the original header (i.e. all the staff) and then have rows for each of those calculations based on today.
Can you provide a sample sheet (NO personal/private/confidential info, i.e. no real names please)
If you can attach it here upload it to OneDrive or SharePoint or the like and share a link or PM it to me and I can post it here.- I should note the calculations are based from today rolling back 365 days worth of absences.
EdwardLui So you say
The part I'm stuck on now is working out the number of occurrences for a typical Mon-Fri work day period. I've used a VBA script but it doesn't count for bank holidays so it's taking that as extra occurrence.
So are you trying to know the # weeks or maybe the # of Workdays in that time period to get an average? something like NETWORKDAYS() ? But if you are looking back at 365 days aren't the # of bank holidays the same every year (i.e. hoidays on Sat/Sun get moved to Fri/Mon)?
- I've been inserting into column A the rows of each date starting from 01/01/2022 to current. Column B onwards is just each staff. Let's say there's 10. If they are off I've been marking the corresponding date/staff cell as 'A' and if its covid related it's 'C'. I've managed to figure out most of it by using the following formula bearing in mind the dates/absence is in one sheet and the reporting is in another sheet but same workbook.
=COUNTIFS('Staff Absence'!$A:$A,"<="&TODAY(),'Staff Absence'!$A:$A,">"&TODAY()-365,'Staff Absence'!B:B,"A")
And changed the letter to C for covid related.
The part I'm stuck on now is working out the number of occurrences for a typical Mon-Fri work day period. I've used a VBA script but it doesn't count for bank holidays so it's taking that as extra occurrence.
It's equally difficult trying to do the same for part-time staff of 3 days. As when off for e.g. 4 weeks, it's showing as 4 different occurrences.
