Forum Discussion
EdwardLui
Jan 10, 2023Copper Contributor
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 absenc...
EdwardLui
Jan 10, 2023Copper Contributor
I should note the calculations are based from today rolling back 365 days worth of absences.
mtarler
Jan 10, 2023Silver Contributor
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)?
- EdwardLuiJan 11, 2023Copper ContributorHey yes that's correct. I'm quite new to using the date formula and functions.
As we're working with actual staff absences we're not working on a basis of an average unfortunately but more exact days. HR requests this too.- mtarlerJan 11, 2023Silver Contributorsorry I'm lost. Which of my comments/questions is correct? If you aren't doing average then what are you doing/trying to get?