12 Month Rolling Calendar for Occurences

New Contributor

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 = Attendance TrackerTab = CalendarTab = Calendar

1 Reply

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.