#### 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

Options
edited 12/09/19

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,

Jim

## Comments

• ✭✭✭✭✭✭
edited 12/24/16
Options

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!

-Gwyneth

• Options

Thank you!

• edited 08/07/18
Options

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]

or

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.

• ✭✭✭✭✭✭
Options

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.

Craig

• Options

That worked perfectly. Thanks so much!

• ✭✭✭✭✭✭
Options
This discussion has been closed.