Date Ranges with Multiple Conditions?

Kelsea Brown

Hello,

I am trying to calculate time windows by referring to previously entered dates, but am unsure how to build a formula that takes into account multiple conditions.

For the highlighted cell, there are 2 conditions that I am trying to incorporate. For the first condition of "4 to 7 days after V3", I have the earliest date as: "=([Date Planned]9 + 4)" and then latest date as "=([Date Planned]9 + 7)". Is there a way to also include the condition "1 to 4 days after V4" in this row? If this was the only condition, the formula for the earliest date would be "=([Date Planned]10 + 1)" and then latest date "=([Date Planned]10 + 4)", but I am unsure of how to combine these with the formulas from the first condition.

Any help would be appreciated!

Genevieve P.

Hi @Kelsea Brown

If I'm understanding you correctly, for the highlighted row, you either want to see 4 days after V3 OR 1 day after V4, depending on which one is earlier. And then for the latest date, either 7 days after V3 OR 4 days after V4, again depending on which one is later.

In this instance you can use the MIN and MAX functions to find the earliest (MIN) or latest (MAX) dates between these two criteria.

Earliest Example:

=MIN([Date Planned]9 + 4, [Date Planned]10 + 1)

and Latest Example:

=MAX([Date Planned]9 + 7, [Date Planned]10 + 4)

Let me know if this works for you!

Cheers,

Genevieve

Genevieve P.

Hi @Kelsea Brown

If I'm understanding you correctly, for the highlighted row, you either want to see 4 days after V3 OR 1 day after V4, depending on which one is earlier. And then for the latest date, either 7 days after V3 OR 4 days after V4, again depending on which one is later.

In this instance you can use the MIN and MAX functions to find the earliest (MIN) or latest (MAX) dates between these two criteria.

Earliest Example:

=MIN([Date Planned]9 + 4, [Date Planned]10 + 1)

and Latest Example:

=MAX([Date Planned]9 + 7, [Date Planned]10 + 4)

Let me know if this works for you!

Cheers,

Genevieve

Kelsea Brown

Hi @Genevieve P this is perfect, thank you!!