# Need Formula Help Multiple Date Columns, count days, if blank choose other column

✭✭✭
edited 08/24/22

I have Multiple date columns for approvals. I want to find the number of days between columns, but some are blank so I need it to choose another date column based on what I set. I've included example data.

Tags:

• ✭✭✭

This is almost perfect. I had to do

=[Person B Approval]@row - MAX([Start Column Date]@row,[Person A Approval]@row)

Basically the same, thank you so much!

• Overachievers

If you're looking for total duration from first approval to last you could use MAX and MIN like:

=MAX([Person A Approval]@row,[Person B Approval]@row, [Person C Approval]@row) - MIN ([Person A Approval]@row,[Person B Approval]@row, [Person C Approval]@row)

If you're trying to do something more complex then you may need to have multiple column formulas with different combinations, or use an Automation to watch the columns for changes and then post a date or value to another column.

BRIAN RICHARDSON | PMO TOOLS AND RESOURCES | HE|HIM

SEATTLE WA, USA

IRON MOUNTAIN

• ✭✭✭

I'm not looking for total duration at the moment, but that is a nice option to have. I am planning on using 5 additional columns. I should have included that there will be a start column. So the formula would be based on the last column to be filled in. Which I know I would have to put in each column name. Here's an example I made manually.

I was thinking something using

=if(isblank( and/or =(if(isdate(

• ✭✭✭

This is so I can take an average in Power Bi and say this is how long Person A takes to approve vs Person D.

• ✭✭✭

Currently I have this working for the first return, now I for Person B, to check Persona A Approval first, then if blank, use State Date.

=IF(AND(ISDATE([Start Date]@row), ISDATE([Person A Approval]@row)), NETWORKDAYS([State Date]@row, [Person A Approval))

• Overachievers

You can streamline it a bit by using MAX if it's always sequential. So for Person B

MAX([Start Column Date]@row,[Person A Approval]@row) - [Person B Approval]@row

BRIAN RICHARDSON | PMO TOOLS AND RESOURCES | HE|HIM

SEATTLE WA, USA

IRON MOUNTAIN

• ✭✭✭

This is almost perfect. I had to do

=[Person B Approval]@row - MAX([Start Column Date]@row,[Person A Approval]@row)

Basically the same, thank you so much!

• Overachievers

@Geilisa Oh heh, I got it backwards. Good catch.

BRIAN RICHARDSON | PMO TOOLS AND RESOURCES | HE|HIM

SEATTLE WA, USA

IRON MOUNTAIN

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!