Question on Counting days "If"

Donna T

Hello,

In my Smartsheet I have a status column that = "RESOLVED" or "IN PROGRESS"

Each row has an "ENTRY" date and sometimes a "RESOLVED" date, if applicable.

If a row is "IN PROGRESS" I want it to count the NETDAYS from today,

but...

If an row is marked "RESOLVED" I would like the NETDAYS function to stop counting.

Ultimately we want to be able to see how many days it takes to resolve a matter. Currently, using the NETDAYS from today, the count continues regardless of whether the matter is "RESOLVED" or "IN PROGRESS."

I am pretty sure there is a nested statement that can accommodate, it's just beyond my skillset.

Thanks,

Donna

Donna T

I changed my approach slightly and used a workaround via nested IF statements in a different manner. Thank you so much for your help -- it got me to thinking about other ways to accomplish what I needed.

Here is the solution I went with: https://community.smartsheet.com/discussion/67615/nested-if-help#latest

Paul Newcome

You can use an IF statement nested inside of your NETDAYS function. Here's the logic behind it.

Basically you are wanting to say that IF [Resolved Date]@row IS a DATE, then use the [Resolved Date]@row, otherwise you want to use TODAY().

=IF(ISDATE([Resolved Date]@row), [Resolved Date]@row, TODAY())

Now that we are generating the correct date to calculate with, we can drop it into your NETDAYS function.

=NETDAYS([Entry Date]@row, end_date_formula)

=NETDAYS([Entry Date]@row, IF(ISDATE([Resolved Date]@row), [Resolved Date]@row, TODAY()))

Donna T

I changed my approach slightly and used a workaround via nested IF statements in a different manner. Thank you so much for your help -- it got me to thinking about other ways to accomplish what I needed.

Here is the solution I went with: https://community.smartsheet.com/discussion/67615/nested-if-help#latest