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.
