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...
mtarler
Jan 10, 2023Silver Contributor
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.
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.
EdwardLui
Jan 10, 2023Copper Contributor
I should note the calculations are based from today rolling back 365 days worth of absences.
- mtarlerJan 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?