Stop Calculating Days After a Cell Changes

KC1551

Hello

Ive been asked to condense 2 worksheets into 1 for workflow simplicity sake. The benefit I had with 2 sheets is that I could set up a formula on one sheet and carry the data over as text.

Now I need to create a formula that chooses which two dates to calculate, based on a cell being populated. Below is what I'm trying to do in one formula.

• If the [Assigned Date] is blank, then calculate the days between the [Created date] and Today
• If the [Assigned Date] is not blank, then calculate the days between the [Created date] and the [Assigned Date]

I've tried a few options that haven't quite worked yet:

=IF(ISBLANK([Assigned Date]@row), NETWORKDAYS([Created Date]@row, TODAY(), [Assigned Date]@row - [Created Date]@row))

=IF(ISBLANK([Assigned Date]@row), NETWORKDAYS([Created Date]@row, TODAY(), OR(NOT(ISBLANK([Assigned Date]@row), [Assigned Date]@row - [Created Date]@row)

=IF(ISBLANK([Assigned Date]@row), NETWORKDAYS([Created Date]@row, TODAY(), [Assigned Date]@row - [Created Date]@row))

Courtney S.

To make it easier try breaking your IF function into the 3 separate parts/functions it should have, to write those out logically, and then combine into the final formula by putting commas between them. If, Then, Else/otherwise:

If Assigned date is blank: ISBLANK([Assigned Date]@row)

Then calculate networkdays between created date and today: NETWORKDAYS([Created Date]@row, TODAY())

Else/otherwise calculate networkdays between created date and assigned date: NETWORKDAYS([Created Date]@row, [Assigned Date]@row)

Put them all together to get: =IF(ISBLANK([Assigned Date]@row), NETWORKDAYS([Created Date]@row, TODAY()),

NETWORKDAYS([Created Date]@row, [Assigned Date]@row))

Here's the IF function page if it's helpful to read through:

IF Function | Smartsheet Learning Center