Calculate the number of days in the different application statuses

Asha Krishnan

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.

Paul Newcome

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

Paul Newcome

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

Asha Krishnan

@Paul Newcome Your formula helped fix the issue that I was experiencing. Thank you so much for the formula!

Paul Newcome

Happy to help. 👍️