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.

Number of Days Ticket is Open

edited 12/09/19 in Archived 2016 Posts

Hello all. I am a newbiw to Smartsheet, and I'm having trouble correctly adding a formula to one of my sheets. I have a repair sheet with a column that shows the date the ticket was created and a column that is labled number of days open. How can I have this column automatically display the number of days the ticket is open based on the current date versus the date the ticket was created?


Thank you,



  • Gwyneth C
    Gwyneth C ✭✭✭✭✭✭
    edited 12/24/16

    Hi JLG3,


    Here's a formula that will display the difference between today's date the date a ticket was opened:


    =TODAY() - [Column Containing Date the Ticket Was Opened]1


    where Column... is the name of the column containing the date (formatted with a Date column type) and 1 is the row number.




    For more information about the TODAY() function and to see more examples for how to use it to calculate future or past dates, see the Using Formulas article in our help center.


    Hope this is what you were looking for!




    Screen Shot 2016-12-24 at 3.59.08 PM.png

    Screen Shot 2016-12-24 at 4.20.54 PM.png

  • JLG3
  • Justin Scott
    edited 08/07/18

    Looking to do something similar with one complication.

    I need a formula that will calculate in a cell either the number of days since a ticket was submitted or if the ticket is complete, the number of days it took to complete:

    Number of days since [Submitted Date] and [Completed Date]


    If [Completed Date] is blank, the number of days since [Submitted Date]

    Hopefully this makes sense and is possible. Thanks for any help!

    I'm currently using the formula "=TODAY() - [Submitted Date]1" but that only works if there isn't a completion date.

    If there is a completion date, I expect to use "=NETDAYS([Submitted Date]2, [Completed Date]2)" but don't know how to indicate to skip the first formula if the [Completed Date] field is blank.

    8-7-2018 8-13-52 AM.png

  • J. Craig Williams
    J. Craig Williams ✭✭✭✭✭✭

    This should work:

    =IFERROR(NETDAYS([Submitted Date]@row, IF(ISBLANK([Completed Date]@row), TODAY(), [Completed Date]@row)), "no submitted date")

    I wrapped in IFERROR() because of blank submission dates. Returning blank ("") works too.

    Welcome to the Community.

    For older posts like this one, you are more likely to get a readers (and thus a response) by posting a new thread and referencing this one. There are not many people following posts from 2016.


  • Justin Scott

    That worked perfectly. Thanks so much!

This discussion has been closed.