Date Columns formula
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
Answers

Try something like this...
=IF(ISDATE(ColumnC@row), ColumnC@row, ColumnB@row)  ColumnA@row

Hello Nick,
so want to calculate either
IF ColumnC =empty
=NETWORKDAY(ColumnA, ColumnB)
or
=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
and about NETWORKDAY here
Hope this helps
Stefan
Smartsheet Consulting, Solution Building, Training and Support.
Projects for Processes and for People.

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

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
Smartsheet Consulting, Solution Building, Training and Support.
Projects for Processes and for People.

@Paul Newcome That works perfectly!
Thank you all for your help!

@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.

@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.
Help Article Resources
Categories
Check out the Formula Handbook template!