Count if singular date falls between a list of date ranges
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
- 63.7K Get Help
- 406 Global Discussions
- 218 Industry Talk
- 457 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 297 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!