Need Formula Help Multiple Date Columns, count days, if blank choose other column
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.
Best Answer
-
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!
Answers
-
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.
-
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))
-
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
-
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!
-
@Geilisa Oh heh, I got it backwards. Good catch.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64K Get Help
- 412 Global Discussions
- 220 Industry Talk
- 459 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 138 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 299 Events
- 38 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!
