Return "Yes" if a date range includes a certain month
I am trying to return either a "Yes" or "No" value in columns for the months of the year if a date range for a project or activity has dates in that month. For example, if a project starts on 2/15/2022 and ends 4/15/2022, I would want to have "Yes" in the February, March, and April columns and "No" in all other month columns for the year. I have tried multiple formulas to try and achieve this, but so far have been unsuccessful.
Has anyone had success with something like this?
I feel like I need several nested OR statements, but can't seem to get my head around it and the closest to success I've been was simply to get a "Yes" in the month for only the start and end dates (while all the months covered by the date range had an incorrect "No").
Best Answer
-
Hi @O.Nash,
Bit of a throwback, but it'd probably be easier to do something like this (again, using Jan 2023 as an example):
=IF(AND([Start Date]@row < DATE(2023, 2, 1) - 1, [End Date]@row >= DATE(2023, 1, 1)), "Yes", "")
Start Date should be less than the 1st of the following month and end date equal to/greater than date of the 1st of the current month.
For December you'd also need to update the year:
=IF(AND([Start Date]@row < DATE(2024, 1, 1) - 1, [End Date]@row >= DATE(2023, 12, 1)), "Yes", "")
Example doing this (cutting out a lot of months just because changing the formula a lot for just a test is effort!):
Hope this helps, but if you encounter any problems/issues then just let us know! 😊
Answers
-
Hi @Victoria Curry,
If your months are in the same year this is certainly doable.
For your January column:
=IF(OR(MONTH([Start Date]@row) = 1, MONTH([End Date]@row) = 1, OR(AND(1 - MONTH([Start Date]@row) > 0, MONTH([End Date]@row) - 1 > 0))), "Yes")
In the following months, substitute the bold numbers in the formula for the relevant month number (2 for February, 3 for March etc.).
Example (only using a few months):
If your project dates and columns spanned several years (e.g. Jan 2023) then you would have to make these statements into AND ones and include the year.
Hope this helps!
-
Hi Nick,
Thanks for a great response. Unfortunately the months are not always in the same year. The sheet is meant to cover a fiscal year, which for us starts in October and ends in September, which means it will always span 2 calendar years. That has been part of my major challenges with figuring out a solution.
-
@Nick Korna Could you elaborate on how you would convert to AND statements and add the Year please? and Thank you :-) - I've been trying and failing to adapt the formula to include the year.
-
Hi @O.Nash,
Bit of a throwback, but it'd probably be easier to do something like this (again, using Jan 2023 as an example):
=IF(AND([Start Date]@row < DATE(2023, 2, 1) - 1, [End Date]@row >= DATE(2023, 1, 1)), "Yes", "")
Start Date should be less than the 1st of the following month and end date equal to/greater than date of the 1st of the current month.
For December you'd also need to update the year:
=IF(AND([Start Date]@row < DATE(2024, 1, 1) - 1, [End Date]@row >= DATE(2023, 12, 1)), "Yes", "")
Example doing this (cutting out a lot of months just because changing the formula a lot for just a test is effort!):
Hope this helps, but if you encounter any problems/issues then just let us know! 😊
-
@Nick Korna YOU ARE A LEGEND!!! Thank you so much. That worked for me absolutely brilliantly! I would never have got there on my own!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.3K Get Help
- 419 Global Discussions
- 221 Industry Talk
- 461 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 142 Just for fun
- 58 Community Job Board
- 462 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!
