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
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
-
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
-
Thank you!
-
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.
-
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
-
That worked perfectly. Thanks so much!
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.1K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 450 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 289 Events
- 34 Webinars
- 7.3K Forum Archives