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

Geilisa

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.

Geilisa

https://community.smartsheet.com/discussion/comment/341187#Comment_341187

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!

Brian_Richardson

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.

Geilisa

https://community.smartsheet.com/discussion/comment/341051#Comment_341051

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(

Geilisa

https://community.smartsheet.com/discussion/comment/341123#Comment_341123

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.

Geilisa

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

Brian_Richardson

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

Geilisa

https://community.smartsheet.com/discussion/comment/341187#Comment_341187

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!

Brian_Richardson

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