Date Ranges with Multiple Conditions?

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!

Find more posts tagged with

Formulas

Accepted answers

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

All comments

Genevieve P.

Hi @Kelsea Brown

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!!

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.