12 Month Rolling Calendar for Occurences

[GJohnson2225]

Hello Everyone,

I've created an excel document to track attendance occurrences. The occurrences are added to the excel tab with the start date in one column and the end date in another. I have another column that has the single occurrence associated with the date. Each row is a unique occurrence for a particular agent by name. On a separate tab, I have a calendar with a drop-down for the agent's name. When the agent's name is selected it shows on the calendar what days the agent received an occurrence. Below is a count of how many occurrences the agent has. I want this occurrence count to count the occurrences based on a 12-month rolling calendar. Once the 12 months have passed it should no longer be counted as an occurrence. This is the formula I'm currently using to calculate the occurrences.

=SUMIF('Attendance Tracker'!B:B,valSelEmployee,'Attendance Tracker'!I:I)

Tab = Attendance TrackerTab = Calendar

[#2]

Hi @GJohnson2225 

you could try with the SUMIFS function:

=SUMIFS('Attendance Tracker'!I:I;'Attendance Tracker'!B:B;valSelEmployee;'Attendance Tracker'!C:C;">="&TODAY()-365)

It takes always the current day ( TODAY() ) and subtracts 365 days.

[#3]

@GJohnson2225 i love this file. I've been looking for something like this for so long. Do you mind sharing it?

[#4]

@GJohnson2225 can you share this spreadsheet?  Just what I'm lookin for?

[#5]

What a great tracker.

[#6]

Hi@GJohnson2225 ,

I would like the formulas you used to create the 12 Month Rolling Calendar for Occurences, and how you created the spreadsheet(s).

[#7]

@GJohnson2225 Would you be able to share this spreadsheet with me? I am not able to create the calendar tab to work like yours and would appreciate the help. Thanks!

[#8]

This is great stuff. This is exactly what I am trying to accomplish. Can someone share this spreadsheet with me please?