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 absenc...
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.
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)?
- Hey 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.- sorry I'm lost. Which of my comments/questions is correct? If you aren't doing average then what are you doing/trying to get?