Welcome to the Smartsheet Forum Archives

The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.

Update status to complete

Jason Lee
Jason Lee
edited 12/09/19 in Archived 2017 Posts


Currently the “Days in House” column uses the Today function to show how many days it’s been since the application was received. I would like it so that when the status is changed to complete, the days in house shows complete as opposed to the date. What formula do I have to use?




  • Rob Hagan
    Rob Hagan ✭✭✭
    edited 03/07/17

    Hi Jason,


    I reckon that this formula may be what you want for [Days in House]1:

    =IF(Status1 = "Complete", Status1, NETWORKDAYS([Date Application Received]1, TODAY()))


    You also may like to put your holidays into the NETWORKDAYS function if you wish to deal with real business days. If you want elapsed days rather than business days, then remove the NETWORKDAYS function and just make it the difference between the dates (plus 1 or not depending on how you consider acting on an application that you received today).





  • Rob Hagan
    Rob Hagan ✭✭✭

    Hi again Jason,


    Also, if you have any reports that draw the Status data from that sheet then unless you open and save that sheet every day (to allow the formula to be re-evaluated because it has the TODAY() function in it) the report may show an "out of date" status.





  • Hi Rob,


    Thank you for your input. I tried the formula but the under the "Days in House" column, it still shows as a number when the status is set to complete. I would like it so that once the status is complete, then it would stop counting the days and just show as "complete." Is there a formula that can do that?

  • J. Craig Williams
    J. Craig Williams ✭✭✭✭✭✭
    edited 03/08/17



    Rob's formula show be in the [Days in House] column and row 1.


    =IF(Status1 = "Complete", Status1, NETWORKDAYS([Date Application Received]1, TODAY()))


    The 1's in the formula are the row number.

    When you copy the formula to row 2, it will look like this:


    =IF(Status2 = "Complete", Status2, NETWORKDAYS([Date Application Received]2, TODAY()))


    Otherwise, it appears the formula should do what Rob provided.


    I would have written this formula:


    =IF(Status1 = "Complete", "Complete", NETWORKDAYS([Date Application Received]1, TODAY()))


    unless there was a requirement to account for other statuses that would result in a blank [Days in House]






This discussion has been closed.