Can I determine if dates fall within a range of other dates?

Bruticus

Hello,

I have a Moratorium and Holiday sheet that I am referencing in a project plan; the goal is to look at the two ranges within the Moratorium and Holiday sheet (Start_Date and End_Date) and determine if either the Start Date and End Date for any given task on a Project Plan falls within that date range in the Moratorium and Holiday reference sheet.

I would imagine I need to use either COUNTIF or COUNTIFS, but not quite sure how to set it up.

In the attached images, the left column is Start Date and the right column is End Date.

Moratorium Dates Example.PNG

Project Plan Dates Example.PNG

Hopefully I explained that well.

Thanks!

Mark

Isaac Jose

Hey Mark,

I brought this use case to my team, and we came up with the following formula:

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

The above formula includes cross-sheet references to the Holiday Start Date column and Holiday End Date column.

The above formula will count the number of holiday rows that overlap with your task (a count of holidays where the start of the holiday is before the end of the task and the end of the holiday is after the start of the task).

From here, you can either use conditional formatting or an IF function to label when that count is greater than zero.

Best,

Isaac

Smartsheet

Technical Account Manager

Jeffrey_WRK

I'm not sure I understand the problem, but here's a possible solution. I put both sheets in one for simplicity. Left of the black column is the Project sheet, the Moratorium and Holidays sheet is on the left.

I use MATCH (search type 1) in a helper column to find the relevant moratorium row. Then in the Moratorium column use and AND function to determine if the dates fall within the range. In your question you wanted to know if the start "and" end dates fell within the range; the key word being AND and no OR.

For the MATCH to work on Moratorium 1, you need a baseline with some earlier date to be the first row. It will actually work if the baseline row is blank as long as there's a blank row 1, but I like the idea of a baseline date.

MATCH Start column: =MATCH([Project Start]@row, Start:Start, 1)

Moratorium column: =IF(AND(INDEX(Start:Start, [MATCH Start]@row) <= [Project Start]@row, [Project End]@row <= INDEX(End:End, [MATCH Start]@row)), "True", "False")

Hope this solves your problem.

Bruticus

Hi Jeffrey,

Thanks so much for your response, this is quite helpful and exactly what I'm trying solve; however, your solution is not quite right.

Tasks 2, 4, 5, and 7 should be true as there is at least one date that falls within the Moratorium range. Tasks 3 and 6 that result to true exactly match those moratorium dates, which is not the expected result I'm looking for.

Thanks again,

Mark

Jeffrey_WRK

See if this matches your objective. Do I understand correctly that Tasks 3 and 6 should be false, or at least never likely to be an option to consider?

MATCH Start: =MATCH([Project Start]@row, Start:Start, 1)

MATCH End: =MATCH([Project End]@row, Start:Start, 1)

Check Start: =IF(AND(INDEX(Start:Start, [MATCH Start]@row) <= [Project Start]@row, [Project Start]@row <= INDEX(End:End, [MATCH Start]@row)), "True", "False")

Check End: =IF(AND(INDEX(Start:Start, [MATCH End]@row) <= [Project End]@row, [Project End]@row <= INDEX(End:End, [MATCH End]@row)), "True", "False")

Moratorium: =IF(CheckStart@row <> CheckEnd@row, "True", "False")

This could be done in a single messy formula, but I prefer extra helper columns that you can hide.

Bruticus

Ultimately, the Moratorium column checks if the Check Start does not equal the Check End; therefore, if Moratorium is False, then that task does fall within a moratorium and if Moratorium is True, then that task does not fall within a moratorium. Do I understand that correctly?

The moratoriums dates you have in the image above are:

Baseline = 01/01/22 - 01/01/22 (Ignore)

Moratorium 1 = 04/04/22 - 04/04/22

Moratorium 2 = 04/23/22 - 04/26/22

Moratorium 3 = 04/30/22 - 04/30/22

Most of your tasks fall within at least one of the moratorium dates:

Task 1 = 04/01/22 - 04/08/22 (04/04/22 falls between these dates, should be flagged)

Task 2 = 04/03/22 - 04/04/22 (04/04/22 is included, should be flagged)

Task 3 = 04/04/22 - 04/04/22 (This date matches a moratorium exactly, should be flagged)

Task 4 = 04/04/22 - 04/05/22 (04/04/22 is included, should be flagged)

Task 5 = 04/22/22 - 04/25/22 (04/23/22 falls between these dates, should be flagged)

Task 6 = 04/23/22 - 04/26/22 (These dates match a moratorium exactly, should be flagged)

Task 7 = 04/24/22 - 04/27/22 (04/26/22 falls between these dates, should be flagged)

Task 8 = 04/27/22 - 04/28/22 (These dates DO NOT fall on or between any moratorium dates,

should NOT be flagged)

Isaac Jose

Hey Mark,

I brought this use case to my team, and we came up with the following formula:

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

The above formula includes cross-sheet references to the Holiday Start Date column and Holiday End Date column.

The above formula will count the number of holiday rows that overlap with your task (a count of holidays where the start of the holiday is before the end of the task and the end of the holiday is after the start of the task).

From here, you can either use conditional formatting or an IF function to label when that count is greater than zero.

Best,

Isaac

Smartsheet

Technical Account Manager

Bruticus

Hey Isaac,

Fantastic! I tested the formula you provided above which works as expected.

Thanks,

Mark