12 Month Rolling Calendar for Occurences

Occasional Visitor

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

3 Replies

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.

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

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