Date Columns formula

Nick Rozaklis

Hello!

I'm new to Smartsheet so I'm hoping you all could help me.

I have three date columns and a duration (how many days between the dates) column.

ColumnA - Initial baseline date

ColumnB - Current EST Date

ColumnC - Actual Date.

Column D - Duration

What I'm wondering is if there's a formula that will enable me to be able have the Duration column calculate the date between ColumnA and ColumnB, UNLESS there is a date in ColumnC. Additionally, if ColumnC has a date entered, then for the duration column to have the number of days between ColumnA and ColumnC and ignore ColumnB.

Thanks!

Nick

Find more posts tagged with

Accepted answers

All comments

Paul Newcome

Try something like this...

=IF(ISDATE(ColumnC@row), ColumnC@row, ColumnB@row) - ColumnA@row

Stefan

Hello Nick,

so want to calculate either

IF ColumnC =empty

=NETWORKDAY(ColumnA, ColumnB)

=NETWORKDAY(ColumnA, ColumnC)

Try this in your Duration Column:

=IF(ColumnC@row ="", NETWORKDAY(ColumnA@row, ColumnB@row), NETWORKDAY(ColumnA@row, ColumnC@row))

More about IF here

https://help.smartsheet.com/de/function/if?dlm=111&frame=0&nav=1

and about NETWORKDAY here

https://help.smartsheet.com/de/function/networkday?dlm=111&frame=0&nav=1

Hope this helps

Stefan

Paul Newcome

@Stefan You can also nest the IF inside of the NETWORKDAY function. It shortens things up a little bit and (in my opinion) makes it easier to read since the first function in the formula is the main function (if that makes sense). Just my personal preference and a different take on things.

=NETWORKDAY(ColumnA@row, IF(ColumnC@row <> "", ColumnC@row, ColumnB@row))

Stefan

Hi Paul,

I agree, very elegant :-)

To be honest, I think for a Smartsheet beginner my (it's actually the standard examples) version is maybe easier to read because you can nearly read it as a normal sentence:

If ColumnC@row = empty, then calculate NETWORKDAY with the first formula, otherwise use the second formula.

Greetings

Stefan

Nick Rozaklis

@Paul Newcome That works perfectly!

Thank you all for your help!

Paul Newcome

@Stefan I agree... Your version is definitely more easily read as a sentence. I still write out certain formulas that way too. That's similar to the logic behind my first solution.

=IF(ISDATE(ColumnC@row), ColumnC@row, ColumnB@row) - ColumnA@row

If ColumnC is a date use that, otherwise use ColumnB. Then subtract ColumnA from that.

Paul Newcome

@Nick Rozaklis Happy to help. 👍️

If you need to account for weekends/holidays, there are two other solutions provided by @Stefan and myself that you could look at as well.

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.