Stop Calculating Days After a Cell Changes

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

Find more posts tagged with

Accepted answers

All comments

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

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.