Check if project start/end dates fall within specific time frame.
My team works on projects that typically take 6-12 months. I'd like to create a roll-up sheet that identifies which quarters in which a project was worked on. I'm creating a new row for each project and linking in the start and end dates from each of the projects' sheets. Then I've created a new column for each quarter (e.g., 2022Q1, 2022Q2, 2022Q3, 2022Q4, 2023Q1...)
Let's say "Project 1" started on Oct 4, 2022 and ended on May 15, 2023.
I'm trying to create a formula that would go in those quarter cells that asks if the dates of the quarter (e.g., 1/1/2022 through 3/31/2022) overlap at all with the dates of the project. If it does, throw a 1 in the cell for that quarter, of not, 0.
So for the Project 1 row, the following columns would have a 1: 2022Q4, 2023Q1, 2023Q2 since the project dates overlap with those quarters. The rest of the columns (for the other quarters) would all be 0.
It seems simple, but I can't seem to get the IF(AND()) formulas working.
Help Article Resources
Check out the Formula Handbook template!