Calculating Number Of Sick Days In A Rolling Year - Exc

It would be far easier to give you an appropriate solution if we had sight of the way you capture sick data in the first place, but the attached is a quick demonstration of how you could do it.
Should point you in the right direction at least.

BSB

Thanks BSB, attached my spreadsheet, are you able to help me?

really appreciated. thanks

Have a look at the blue cells in the attached.
Does that help at all?

BSB

Thanks, for example; name1 should have 2 occasions, but the blue cells has counted 0? are you able to fix the formula?

Many Thanks again

San

As of today's date a rolling year would start at 21/08/2014, therefore, any instances of sickness "Name 1" has recorded in your data would pre-date this rolling year and the numbers in the blue cells are correct.

BSB

ok i see thank you. I also need a formula to work out number of occasions they have been off sick, would you be able to help?

Many thanks

San

If someone was off sick for 2 days would you consider that 1 occasion?
And do you need occasions within the rolling year?

BSB

Hi, yes i would count that as 1 occasion and yes it would be great to have the occasions within the rolling year.

Many BSB

Your current sample data would give the same results for occasions as days as all instances are only one day long.
So I've changed the date in the green cell to provide a 2 day sickness and added a new blue column for occasions.

BSB

For counting occasions, the below formula in cell L3 and copied down would be better.

Formula:

Please Login or Register  to view this content.

Does the same thing, just more efficient.

BSB

Many thanks, very appreciated your help

Really appreciated if anyone could help solve this problem? thanks

In K3 and copy down:

Formula:

Please Login or Register  to view this content.

Change the 100's in the formula to a suitable row number as necessary.
You could set this up with dynamic named ranges so you don't have to make that adjustment manually. It would adapt along with the data as more rows are added.


BSB

Thanks BSB. it works! you are a star. thank you

Hi BSB

link to the spreadsheet, I wander if you know about if/vlookup statement

attached spreadsheet.

Many thanks

San

Hi San,

I'm pretty sure I know the answer to this latest question, but forum rules dictate that you ask a new question in a new thread.

Do that then PM me when done and I'll pop up with the answer.

BSB