Formula

Aubrey Ferreira

Hi

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

I have four types of statuses that should be shown:

1. Planning,
2. Execution,
3. Monitoring,
4. 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.

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