Formula to Calculate Occurrences of Staff Absence

Hello everyone,

I hope you can help, as I've exhausted both the limit of my knowledge of arrays, and endless searching to solve this.

I have created a major spreadsheet to track staff sickness absences (I have attached a very basic example here.) Counting the numbers of days is the easiest thing. I also need to capture the number of occurences within a year (so, if sick for an entire week, or fortnight, that's one occurrence - fairly standard Absence Management, I think.)

I've finally managed to get a formula that counts the number of occurences for full-time staff, and to ignore weekends that fall within a period of absence (i.e if someone is sickness on a Friday and trhe following Monday.) None of the staff works weekends. I've included that formula in the example.

The difficulty I face is that some staff have days within the week that they don't work, so cannot be included as a day sick. Some staff don't work Monday each week, some Thursday etc. There are a few that don't work for 2 days each week, and those days will also vary. I need the formula to ignore these days as it would the weekends but in addition to the weekends. I hope that makes sense.

I've also included a little matrix within the example that illustrates this. I have tried using this matrix to assist with the formula, but I'm failing.

I'm hoping someone may be able to help with a standard formula I can use without tailoring it for each person.

Many thanks

Steve

Bernie,

Thank you so much for the response.

I knew I perhaps wouldn't make things clear in my original posting. It's not actually the Non-Working periods I need to colour code. I only put these in yellow as a visual aid to show the Non-Working dates that should be ignored when calculating consecutive "sick" days.

The formula I'm trying to improve on is in far right column of the spreadsheet in Column AG. I've simply highlighted this in green to make it clear.

I'm far from being adept at VBA, but despite having said the above, your code has inspired me to include this in the spreadsheets I'm working on. Could this be adapted to take the Non-Working days from the small matrix in in B18:N20 for each person. This is the key which shows what days people work each week and is kept in a separate worksheet in my spreadsheets. The top grid is part of the monthly view which managers use.

Many thanks again.

Steve

Hello Bernie,

This is looking really good now! Thank you. I've tested it on various combinations of Non-Working Days and most of them are coming out exactly as I expect. Just a couple of anomalies I can't figure out.

Just one thing I noticed. If someone has no days sick, the occurrence (or SickPeriod) always returns 1 instead of 0.

I've tried a couple of changes to the code, but as I said, I'm really not confident enough with VBA, and I can't get it to correct this. I don't know whether the SickPeriods variable initially defaults to 1 somewhere, or it's to do with the boolean logic on the first pass.

And, as if I haven't asked you enough ... going back to the original point, is there a way I can also use this VBA to highlight the cells in colour in the main view (for an instant guide to the managers rather than them having to look at the table each time - which I keep in a separate worksheet) but still allow input of hours?

I appreciate all your time and obvious genius on this one Bernie. I'm am so much closer to getting this working than I was a few days ago thanks to you.

Kindest reagrds
Steve

Hi Bernie

I've checked all those things, but still can't see anything. I must be missing something!

I've re-attached the sample sample sheet. The only difference on my main spreadsheet is that I have zeroes instead of blanks, but I've adjusted that, and it works the same, apart from the 1 for No Sickness.

Would you be kind enough to have a look and tell me what's clearly staring me in the face.

And thank you for the CF advice. I tried that previously, but didn't quite get it right. I'll try yours.

Many thanks again

Steve

Hi Bernie,

I think I see where the anomaly might be.

In my very basic example, If there's a number in there, it means they were off sick that day for 7.4 hours, rather than the other way around.

So, as I was poorly experimenting with earlier, do I just need to switch the boolean round to determine which is True or False to reflect this?

And your CF example works beautifully, even in my main files with 300 staff in it. Thank you so much!

Steve

Bernie,

That's so much better!! Although it seems to have thrown Duncans' count from 3 to 5 Sickperiods.

But listen, I've taken up more than enough of your time, and you've given me so much more than I originally asked, so I'll go away and see if I can figure this last tiny piece of the puzzle out. As you say, it's no doubt something I,ve done wrong.

I know this sort of stuff is probably standard routine to you, but I can't adequately tell you how grateful I am for both your patience and jaw-dropping (to me!) knowledge that makes this Forum and its Excel Expeerts so wonderful.

Thank you so much Bernie!

With the kindest regards

Steve

Bernie,

PERFECT!! Absolutely spot on across the board.

You are outstanding. I don't think I would have spotted that, so you've saved me!

Again, thank you so much. You've inspired me to stop being nervous about VBA.

Steve

Hello, i have the exact same problem. Where in column A i have a list of agent names, column d onwards are days of the year.if an agent is reported as S and then followed my a day off then S. I need the day off to auto amend to S