Need Smartsheet Formula Help

mrclean

Hi everyone I am hoping you can help me. I am developing a worksheet that will track how long an items is in each stage. I have four main stages: Time in recruiting, Time in creds, Time awaiting final approval, and time pending start. The time measurement is all in days. I am trying to add a formula so that the number of days shows using today. For example, lets say there is no date in the creds, awaiting final approval or pending start, the time in recruiting would use the formula TODAY - Date added to sheet.

The problem I am running into is when there is a date in creds, and than there is date in awaiting final approval how can I calculate the time in each stage? Any help would be appreciated.

Paul Newcome

Try something like this (update column names as needed):

=IFERROR(IF([End Date]@row <> "", [End Date]@row, TODAY()) - [Start Date]@row, "")

Paul Newcome

Happy to help. 👍️

Paul Newcome

Are you using separate columns to track the number of days in each stage? Are your date columns for each stage the start or end of the stage? Are you able to provide some screenshots for context?

mrclean

Hi sir! I apologize for the delay in responding. I have listed below some information as well as screen shot which am hoping can assist.

I need the formula to show how many days a person was in recruiting, in credentialing, Pending Approval and Pending start. All the columns where the user enters information are Date Columns

In recruiting the time starts from the [Date Position Added to Sheet] and stops when a date is entered in [Recruiting Sent to Creds]. In Credentialing starts when a date is entered in [Recruiting Sent to Creds] and stops when a date is entered into [Pending Approval]. The Pending Approval stage starts when a date is entered in [Pending Approval] and stops when a date is entered in [Pending Start].

Sir it seems pretty startight forward but everything I have tried gives me some type of issue. I have placed a screenshot below and any help would be appreciated.

Brian

Paul Newcome

Try something like this (update column names as needed):

=IFERROR(IF([End Date]@row <> "", [End Date]@row, TODAY()) - [Start Date]@row, "")

mrclean

Thank you so much sir!

Paul Newcome

Happy to help. 👍️