Number of Days Ticket is Open

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

Find more posts tagged with

Government

Solution Center

Conditional Formatting

Project Management

Comments

Gwyneth C

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

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

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

JLG3

Thank you!

Justin Scott

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

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

Justin Scott

That worked perfectly. Thanks so much!

J. Craig Williams