Counting Sick Days using rolling 12 month calendar

[bowdropper]

Hi Everyone,

I am creating an attendance tracker that needs to be able to count all sick occurrences ('SO' in the spreadsheet) during a rolling year, starting today and going back 1 year. (Ex. I need to count Person 'A's sick occurrences from last Jan 13 2019 to Jan 13 2020.) It would need to adjust the range each day.

I have been able to figure out how to create a range that counts backwards from a certain cell using OFFSET, but I cannot make it adjust each day. Any help would be greatly appreciated!

Column 'I' is my attempt at the formula.

Thanks,

Mike

[humdingaling]

no sure how are you working how what "today" is on the spreadsheet?

as have not specified in your example i have added to cell C1 formula =Today()
you can replace with something manual if you do want to "fix" what today is

your sample spreadsheet doesn't really show enough of spreadsheet to do rolling 12 months
so i will show rolling 12 days instead for concept sake

put large comment to explain the formula used

Please Login or Register  to view this content.

OFFSET(K3,0,MATCH($C$1,$K$1:$AN$1,0),,-13)

K3,0,18,0,-13

K3 is starting point
0 rows from starting point
column right from starting point is determined by what value in C1 is

13th Jan = 17
14th Jan = 18
15th Jan = 19
etc

-13 just counts back 12 cells/days to the left
For 12 months you would need to count back 366...

[FlameRetired]

I put TODAY's date in C1 as well.

Then this in I3 and filled down.

Please Login or Register  to view this content.

[bowdropper]

That is exactly what I needed! Thanks for the help!

[FlameRetired]

Glad you found solution, and thank you for marking your thread Solved. Just out of curiosity what did you use?

[bowdropper]

I actually did not see your post until after I marked this as solved. So I'm using humdingaling's solution.
Out of curiosity, I tested both solutions and both worked. Any idea which formula requires less computing power?

[FlameRetired]

No I don't.

Too often the answers begin with ... "it depends".

Of note though. The OFFSET function is volatile. This usually isn't an issue, but when it becomes one it can be frustrating and maybe confusing if you are not aware volatility, its effects, what to look for and how to remedy it. That said you might find this article helpful.

[humdingaling]

i used offset because that's the method you had started out with and it seemed that you understood the principles behind the formula

Depending on your actual spreadsheet size (in terms of data) you may or may not actually see the difference

The sumproduct solution "should" use computing power for the reasons pointed out by Dave
if you are working with large data set and you understand sumproduct works (to me, understanding offset was easier than sumproduct)
than i would suggest using his solution, its far more robust and easier to "fix" should it be required