Count if singular date falls between a list of date ranges
![Sam Swain](https://us.v-cdn.net/6031209/uploads/defaultavatar/nWRMFRX6I99I6.jpg)
Hi community,
I want to check if a singular date falls between any of a list of different date ranges from another sheet.
E.g. If 01/08/23 is found in any of a list of start / finish date ranges, I want to flag or count.
Any suggestions?
Best Answers
-
Try a COUNTIFS.
=COUNTIFS({Start Date Column}, @cell<= [Date Column name]@row, {End Date Column}, @cell>= [Date Column Name]@row)
-
You would add that range/criteria set to the original COUNTIFS.
Answers
-
Try a COUNTIFS.
=COUNTIFS({Start Date Column}, @cell<= [Date Column name]@row, {End Date Column}, @cell>= [Date Column Name]@row)
-
Hi Paul, that works. I went with this in the end
=IF(COUNTIFS({Start Date}, @cell <= Dates@row, {Finish}, @cell >= Dates@row) > 0, "Scheduled", "Available")))
However...I also want the formula to only check the date range in the other sheet if it is a child row. Having difficulty doing this.
I've got a helper column in the other sheet (which you gave me before).
This helper column has broadcast or maintenance shown in the cell where it is a child. I want the formula to know to only cross reference the date range when it is a child or it has broadcast / maintenance in the helper column.
Hope that makes sense
-
You would add that range/criteria set to the original COUNTIFS.
-
Thanks Paul! I'm improving, slowly!
-
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.1K Get Help
- 348 Global Discussions
- 199 Industry Talk
- 427 Announcements
- 4.4K Ideas & Feature Requests
- 133 Brandfolder
- 127 Just for fun
- 127 Community Job Board
- 455 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 282 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!