Count if singular date falls between a list of date ranges

Sam Swain

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?

Paul Newcome

Try a COUNTIFS.

=COUNTIFS({Start Date Column}, @cell<= [Date Column name]@row, {End Date Column}, @cell>= [Date Column Name]@row)

Paul Newcome

You would add that range/criteria set to the original COUNTIFS.

Paul Newcome

Try a COUNTIFS.

=COUNTIFS({Start Date Column}, @cell<= [Date Column name]@row, {End Date Column}, @cell>= [Date Column Name]@row)

Sam Swain

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

Paul Newcome

You would add that range/criteria set to the original COUNTIFS.

Sam Swain

Thanks Paul! I'm improving, slowly!

Paul Newcome

Happy to help. 👍️