Check if project start/end dates fall within specific time frame.

gunnell

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.

Thanks!

Paul Newcome

Try something like this:

=IF(AND([End Date]@row>= DATE(quarter start date), [Start Date]@row<= DATE(quarter end date)), 1)

Using the proper syntax to input the appropriate quarter start and end dates should get you what you are looking for.

DATE(yyyy, mm, dd)

gunnell

Try something like this:<\/p>

=IF(AND([End Date]@row>= DATE(quarter start date), [Start Date]@row<= DATE(quarter end date)), 1)<\/p>

Using the proper syntax to input the appropriate quarter start and end dates should get you what you are looking for.<\/p>

DATE(yyyy, mm, dd)<\/p>","bodyRaw":"[{\"insert\":\"Try something like this:\\n=IF(AND([End Date]@row>= DATE(quarter start date), [Start Date]@row<= DATE(quarter end date)), 1)\\n\\nUsing the proper syntax to input the appropriate quarter start and end dates should get you what you are looking for.\\nDATE(yyyy, mm, dd)\\n\"}]","format":"rich","dateInserted":"2023-06-09T13:54:21+00:00","insertUser":{"userID":45516,"name":"Paul Newcome","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20Newcome","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/082\/nQPUTVFKKWDJ2.jpg","dateLastActive":"2023-08-03T13:06:14+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"displayOptions":{"showUserLabel":false,"showCompactUserInfo":true,"showDiscussionLink":false,"showPostLink":false,"showCategoryLink":false,"renderFullContent":false,"expandByDefault":false},"url":"https:\/\/community.smartsheet.com\/discussion\/comment\/379822#Comment_379822","embedType":"quote"}"> https://community.smartsheet.com/discussion/comment/379822#Comment_379822

The problem I was having with something similar to what you have is that it wasn't picking up projects that started before the quarter and ended after the quarter. So if project started in 2022Q4, continued in 2023Q1, and ended during 2023Q2, I would want a "1" for all three quarters but it was only giving me one for 2022Q4 and 2023Q2 (skipping 2023Q1).

I actually just got this working late last night (with the help of ChatGPT). Here is what I ended up with. This one checks for 2022Q1.

=IF(OR(AND([email protected]<= DATE(2022, 3, 31),[email protected]>= DATE(2022, 1, 1)), AND(DATE(2022, 1, 1) <=[email protected], DATE(2022, 3, 31) >=[email protected])), 1, 0)

Paul Newcome

Try something like this:

=IF(AND([End Date]@row>= DATE(quarter start date), [Start Date]@row<= DATE(quarter end date)), 1)

Using the proper syntax to input the appropriate quarter start and end dates should get you what you are looking for.

DATE(yyyy, mm, dd)

gunnell

Try something like this:<\/p>

=IF(AND([End Date]@row>= DATE(quarter start date), [Start Date]@row<= DATE(quarter end date)), 1)<\/p>

Using the proper syntax to input the appropriate quarter start and end dates should get you what you are looking for.<\/p>

DATE(yyyy, mm, dd)<\/p>","bodyRaw":"[{\"insert\":\"Try something like this:\\n=IF(AND([End Date]@row>= DATE(quarter start date), [Start Date]@row<= DATE(quarter end date)), 1)\\n\\nUsing the proper syntax to input the appropriate quarter start and end dates should get you what you are looking for.\\nDATE(yyyy, mm, dd)\\n\"}]","format":"rich","dateInserted":"2023-06-09T13:54:21+00:00","insertUser":{"userID":45516,"name":"Paul Newcome","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20Newcome","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/082\/nQPUTVFKKWDJ2.jpg","dateLastActive":"2023-08-03T13:06:14+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"displayOptions":{"showUserLabel":false,"showCompactUserInfo":true,"showDiscussionLink":false,"showPostLink":false,"showCategoryLink":false,"renderFullContent":false,"expandByDefault":false},"url":"https:\/\/community.smartsheet.com\/discussion\/comment\/379822#Comment_379822","embedType":"quote"}"> https://community.smartsheet.com/discussion/comment/379822#Comment_379822

The problem I was having with something similar to what you have is that it wasn't picking up projects that started before the quarter and ended after the quarter. So if project started in 2022Q4, continued in 2023Q1, and ended during 2023Q2, I would want a "1" for all three quarters but it was only giving me one for 2022Q4 and 2023Q2 (skipping 2023Q1).

I actually just got this working late last night (with the help of ChatGPT). Here is what I ended up with. This one checks for 2022Q1.

=IF(OR(AND([email protected]<= DATE(2022, 3, 31),[email protected]>= DATE(2022, 1, 1)), AND(DATE(2022, 1, 1) <=[email protected], DATE(2022, 3, 31) >=[email protected])), 1, 0)

Paul Newcome

@gunnellThe key to mine is comparing [End Date] to after the START of the quarter and [Start Date] to before the END of the quarter. This takes care of any overlap and will pick up everything in between with only two pieces to the AND function instead of 4 with the OR.

gunnell

@Paul NewcomeYes! Simple is better! Thank you!

Paul Newcome

Happy to help.️