How to create monthly report for activities based on created date, closed date, and due date, for past year?

[josevaldesv1]

Hi everyone,

I'm stuck: For each of past 12 months, identify which activities were created, were closed on time, were closed overdue, were previously opened and remained open during that month, were previously open and not yet closed but already overdue.

I'm using a lot of IF statements, someone talked about sumproduct, others told me about IFS, in the end, I am stuck. I wish I could find a way to do it in Pivot Table, if possible.

Anyone knows a good way to create some formulas to come up with a good solution?

This is what I have:

|Activity ID|Open date|Due Date|Closed Date|Expected logic if we were to look at the month of February 2024:|



|ID|Date Created|Due Date|Date Closed|Example of logic expected to be used to sum totals per month|
|:-|:-|:-|:-|:-|
|1|January 1, 2020|August 5, 2024||Open, not overdue|
|2|January 1, 2020|August 5, 2024|Feb 2, 2024|Closed on time|
|3|January 1, 2020|January 5th, 2024|Feb 2, 2024|Closed overdue|
|4|March 5, 2024|March 7th, 2024||Not yet opened|
|5|Feb 8th, 2024|Feb 9th, 2024||Created this month, not yet closed|
|6|Feb 8th, 2024| Feb 9th, 2024|March 1, 2024|Created this month and overdue|
|7|Feb 8th, 2024| Feb 8th, 2024|Feb 9th, 2024|Created this month and closed overdue|
|8|Feb 8th, 2024|Feb 9th, 202\*4\*|Feb 27th, 2024|Created this month and closed on time|



and this is what I need for the past 12 months:



|Status / Month|June 2023|.... each month in between|April 2024|May 2024|
|:-|:-|:-|:-|:-|
|Created this month|||||
|Created and closed this month|||||
|Created and closed overdue this month|||||
|Closed this month|||||
|Closed overdue this month|||||
|Open during this month|||||
|Open overdue during this month|||||

 

[josevaldesv1]

Didn't know how to create the tables.
But you can take a look here:

https://docs.google.com/spreadsheets/u/0/d/1EptxTcwNEkyjGwI7_A3jLGH7l25IV2P3/htmlview?pli=1