# Date Columns formula

Options

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

• ✭✭✭✭✭✭
Options

Try something like this...

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

• ✭✭✭✭✭✭
Options

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

Hope this helps

Stefan

Smartsheet Consulting, Solution Building, Training and Support.

Projects for Processes and for People.

• ✭✭✭✭✭✭
Options

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

• ✭✭✭✭✭✭
Options

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.

• Options

@Paul Newcome That works perfectly!

Thank you all for your help!

• ✭✭✭✭✭✭
Options

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

• ✭✭✭✭✭✭
Options

@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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!