Calculate the number of days in the different application statuses
![Asha Krishnan](https://us.v-cdn.net/6031209/uploads/defaultavatar/nWRMFRX6I99I6.jpg)
I want to measure the number of days an application stays in the different application statuses. For this purpose, I have set up automated workflows to record a date in the corresponding date columns for each status change. When a date field is blank, it either indicates that the application skipped that status, or it is yet to be filled in by Smartsheet. The dates will be in chronological order. I have included a table below with some example data.
Processing Date | Days in Processing | Pending Date | Days in Pending | Site Visit Date | Days in Site Visit | Approval/Denial Date |
---|---|---|---|---|---|---|
6/3/2024 | 2 | 6/5/2024 | 2 | 6/7/2024 | 2 | 6/9/2024 |
6/10/2024 | 4 |
|
| 6/14/2024 | 2 | 6/16/2024 |
6/17/2024 | 6 |
|
|
|
| 6/23/2024 |
6/24/2024 | 2 | 6/26/2024 |
The formula in “Days in Processing” column:
=MIN([Pending Date]@row, [Site Visit Date]@row, [Approval/Denial Date]@row) - [Processing Date]@row
The formula in “Days in Pending” column:
=MIN([Site Visit Date]@row, [Approval/Denial Date]@row) - [Pending Date]@row
The issue that I am experiencing here is when all the date fields included in the formula are left blank, Smartsheet outputs a '0' in the days in status field. I would prefer to leave it blank because '0' would indicate that the application was moved to the next status on the same day.
How can this be accomplished? I would really appreciate your help.
Answers
-
You would use an IF/AND to say that if all are blank then output blank.
=IF(AND([Column A]@row = "", [Column B]@row = ""), "", MIN(……………))
Help Article Resources
Categories
Check out the Formula Handbook template!