Formula

Aubrey Ferreira

I need to return a status that references today between date ranges:

I have four types of statuses that should be shown:

Planning,
Execution,
Monitoring,
Closing.

Let have the following conditions:

If Today is between Start Date 1 and Finish Date 1 - Planning should be reflected,

If Today is between Start Date 2 and Finish Date 2 - Execution should be reflected,

If Today is between Start Date 3 and Finish Date 3 - Monitoring should be reflected,

If Today is between Start Date 4 and Finish Date 4 - Closing should be reflected,

The formula will be in the top row of the sheet and will have to reference several rows to determine the status.

Pls help on a formula.

Find more posts tagged with

Accepted answers

All comments

Paul Newcome

Can you provide a screenshot of the sheet with sensitive/confidential data removed, blocked, and/or replaced with "dummy data" as needed as well as more detail such as which rows exactly are being referenced?

Aubrey Ferreira

Paul Newcome

Hmm... Are you trying to pull the [Task Name] that is labeled as "In Progress"?

=INDEX(CHILDREN([Task Name]@row), MATCH("In Progress", CHILDREN(Status@row), 0))

That is much more simple than trying to use both date columns in a nested IF statement and keeps things generic enough to allow for child rows to be added/deleted within each of the phases.

Aubrey Ferreira

Hi, thank you. I will try this and see if it works. Regards

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, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.