Formula to identify whether or not a project starts or ends in a Quarter
Hello,
I have columns for each quarter of the year (Q1, Q2, etc.) in Checkbox format. I am trying to create a formula where the Checkbox is automatically checked if the Start Date and/or End Date fall within the quarter.
For Q1, I only need to look at the start date because all of the Start Dates are in 2022.
=IF(AND([Start Date]@row >= DATE(2022, 1, 1), [Start Date]@row <= DATE(2022, 3, 31)), true, false)
I run into trouble for the Q2 where the project may have started in Q1 but will run into Q2, so I need to consider the End Date as well. I tried the following as well as other variations but I get the #incorrect argument error.
=IF((AND([Start Date]@row >= DATE(2022, 4, 1), [Start Date]@row <= DATE(2022, 6, 30))), true, IF(AND([End Date]@row >= DATE(2022, 4, 1), [End Date]@row <= DATE(2022, 6, 30))), true, false)
Please let me know if I need more or less parenthesis, or if I need an OR thrown in there. Thank you in advance.
Best Answer
-
OK, l solved it and am just leaving this for others if they need it. Instead of putting the End Date in the False section, I used OR
=IF(OR((AND([Start Date]@row >= DATE(2022, 4, 1), [Start Date]@row <= DATE(2022, 6, 30))), (AND([End Date]@row >= DATE(2022, 4, 1), [End Date]@row <= DATE(2022, 6, 30)))), true, false)
Answers
-
Almost as soon as I posted this, I solved half of my problem. I added a second IF at the beginning. This works for the rows that have Start and End Dates completely within Q2, but any rows that start and/or end outside of Q2 still give the #Incorrect Argument.
=IF(IF(AND([Start Date]@row >= DATE(2022, 4, 1), [Start Date]@row <= DATE(2022, 6, 30))), true, IF(AND([End Date]@row >= DATE(2022, 4, 1), [End Date]@row <= DATE(2022, 6, 30))), true, false)
Again, thank you for any assistance.
-
OK, l solved it and am just leaving this for others if they need it. Instead of putting the End Date in the False section, I used OR
=IF(OR((AND([Start Date]@row >= DATE(2022, 4, 1), [Start Date]@row <= DATE(2022, 6, 30))), (AND([End Date]@row >= DATE(2022, 4, 1), [End Date]@row <= DATE(2022, 6, 30)))), true, false)
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!