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'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.
=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.