Determine which quarters a project is active in.

kramezani

Hello,

I have a table with a list of projects and their start and end dates. I want to ultimately create a report which will be displayed within a Dashboard chart showing how many active projects I have during a given quarter and fiscal year. I was thinking to create a column in my table for each quarter (Qtr 1 FY23, Qtr 2 FY23, etc) and then in each of those columns there will be a formula that will look at the start and end dates. If that quarter falls within those start and end dates then it can output "True" or "Yes." Later I will make a report that will count how many "True" there is in a quarter.

I just can't come up with the solution for this though. Does anyone have any ideas? The quarter's will be represented as months. So Quarter 1 will be months 7, 8, 9 since my fiscal year starts in July. So I need a formula that looks at the Start and Finish date range and determines whether month's 7, 8, or 9 are within that range AND the fiscal year matches as well. So it has to look at the year and month to validate. Look forward to seeing possible solutions. THANKS!

EDIT: I should be clear that the Start and Finish dates that I am referencing are linked cells. These are cells linked to construction schedules. So I am not working with date columns.

Jeff Reisman

OK so you're more concerned with if the quarter falls within the project dates, not if the project dates fall within the quarter. Not a problem - just need to flip the criteria around:

=IF(AND(RealStart@row <= DATE(2022, 9, 30), (RealFinish@row >= DATE(2022, 7, 1)), "True", "")

In English: IF the Start date is less than or equal to the last day of Qtr 1 FY23, AND the Finish date is greater than or equal to the first day of Qtr 1 FY23, set the cell value to "True"; otherwise, leave the cell blank.

As long as the start date is before the end of the quarter, and the finish date is after the start of the quarter, then the project was active during the quarter.

Jeff Reisman

So the first thing you'll need to do (to do this right) is create two helper date-type columns for converting the linked cell "dates" into real dates. You can use the DATE function for this. I can help with this if you show me what your "date" values look like. Call these columns RealStart and RealFinish - they can be hidden later.

In the Qtr 1 FY23 column:

=IF(AND(RealStart@row >= DATE(2022, 7, 1), RealFinish@row <= DATE(2022, 9, 30)), "True", "")

In English: IF the start date is on or after 7/1/22 AND the finish date is on or before 9/3/22, set the cell value to "True"; otherwise, leave the cell blank.

Repeat for the other quarters, just change the start and end dates.

Of course, if your start and end dates can cross more than one quarter, we'll have to change the criteria up a good bit:

=IF(OR(AND(RealStart@row >= DATE(2022, 7, 1), RealStart@row <= DATE(2022, 9, 30)), AND(RealFinish@row >= DATE(2022, 7, 1), RealFinish@row <= DATE(2022, 9, 30))), "True", "")

In English: IF the start date is on or after 7/1/22 AND on or before 9/30/22, OR if the finish date is on or after 7/1/22 AND on or before 9/30/22, then set the cell value to "True"; otherwise, leave it blank.

kramezani

Hi Jeff,

First of all, thank you for your response.

Also, these projects span multiple years so yes they do cross more than one quarter. I actually came up with the same formula you did prior to posting my question but it never worked. The reason why it doesn't work is because I am not interested in only the start and finish dates. I am interested in all of the dates this project spans. This formula will only tell me if the start or end dates occur within a specified fiscal quarter. I need to know if a smaller date range (a quarter - 3 months) falls within a longer date range (duration of the entire project). I think it's going to require additional steps. Am I right about this?

Jeff Reisman

OK so you're more concerned with if the quarter falls within the project dates, not if the project dates fall within the quarter. Not a problem - just need to flip the criteria around:

=IF(AND(RealStart@row <= DATE(2022, 9, 30), (RealFinish@row >= DATE(2022, 7, 1)), "True", "")

In English: IF the Start date is less than or equal to the last day of Qtr 1 FY23, AND the Finish date is greater than or equal to the first day of Qtr 1 FY23, set the cell value to "True"; otherwise, leave the cell blank.

As long as the start date is before the end of the quarter, and the finish date is after the start of the quarter, then the project was active during the quarter.

kramezani

Jeff,

That was awesome. For some reason I just couldn't wrap my head around the logic but it's so simple haha. I believe you have an extra "(" right before the second logical "AND" expression. Thanks!!!

Jeff Reisman

https://community.smartsheet.com/discussion/comment/325976#Comment_325976

Yes, you're right I do have an extra parentheses! Good catch.