# 12 Month Rolling Calendar for Occurences

Copper Contributor

# 12 Month Rolling Calendar for Occurences

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

7 Replies

# Re: 12 Month Rolling Calendar for Occurences

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.

# Re: 12 Month Rolling Calendar for Occurences

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

# Re: 12 Month Rolling Calendar for Occurences

What a great tracker.

# Re: 12 Month Rolling Calendar for Occurences

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

# Re: 12 Month Rolling Calendar for Occurences

@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!