Our head of cover wants a report that will show how many teaching staff were off on any given week last half term. Last time I ran this report, I had to manually change the spreadsheet and it took hours, and if this is going to become a regular occurrence, I would love to have a more automated system. So I guess my questions are:
1, Is there a way/report in SIMS or Discover that will give me the number of staff absences per week? I've looked, and can see nothing obvious, though I believe that Discover may be improving this aspect in later updates.
2, Does anyone know of a way to extrapolate from a start date and end date the number of days off in any given week. I've looked at SUMPRODUCT, NETWORKDAYS, COUNTIFS but I can't get my head around allocating days off to specific weeks when the days of spread over a weekend. If it's a single day, then it's very easy. But when we get to multiple days, and then multiple days over multiple weeks, it becomes a real noodle scratcher.
For example, lets say someone was ill from the 04/09/13 to 11/09/13. I can work out that it is 6 working (network) days that have been lost, and which week the start and end dates fall into. Beyond that, I'm stuck. Ideally I'd like to count the number of network days between the start and end date of the illness period, IF they fall between the date range of Week 1, and then again for week 2, 3 etc... But my excel-fu fails me :confused:
Any help gratefully received!
Should note you need permissions to manage cover for that menu route.
Tools -> Cover -> Statistics -> Absence
Seems an odd place to put a report, but thank you so much! :)
Also for your broader analysis needs take a look here. He was actively developing a while back but i think has slowed now. There are a few threads on here on the subject so you could check those and contact him if need be.
Absence & Cover Analysis | Weird & Wonderful IT