How to calculate days a ticket is open/closed

adegeus101536

I am trying to calculate how many days a ticket remains open but I also need that formula to stop counting if the "Ticket Closed Date" column is filled in. I have tried a couple but they keep coming back as errors. There are multiple selections for the Status of Ticket column... Work Has Not Started, Work Has Begun, Awaiting Parts to Complete, Work is Completed. When work is completed I would like it to calculate how many days passed from the Request Date column to the Ticket Closed Date column. Is anyone able to help me out?

Mark Cronk

I used different column names than your sheet had. Screenshot helped. Confirm that the formula column names match your actuals and try:

=IF(AND([ticket status]@row= "Work is Completed", ISDATE([ticket closed date]@row)=1) , [ticket closed date]@row-[request date]@row, today()-[request date]@row)

Work?

Mark

Mark Cronk

Hi @adegeus101536 ,

Try:

=IF(AND([status]@row= "Work is Completed", ISDATE([ticket close date]@row)=1) , [ticket closed date]@row-[ticket request date]@row, today()-[ticket request date]@row)

You may need to adjust columns names to match your actual.

Work?

Mark

adegeus101536

Screenshot 2021-01-09 150521.jpg

@Mark Cronk I'm usually not this bad with formulas! It is still coming back unparseable. I included a picture!

Mark Cronk

I used different column names than your sheet had. Screenshot helped. Confirm that the formula column names match your actuals and try:

=IF(AND([ticket status]@row= "Work is Completed", ISDATE([ticket closed date]@row)=1) , [ticket closed date]@row-[request date]@row, today()-[request date]@row)

Work?

Mark

Andrée Starå

Hi @adegeus101536

To add to Mark's excellent advice/answer.

Here's another option.

=IF([Ticket Closed Date]@row <> "", [Ticket Closed Date]@row - [Request Date]@row, IF([Request Date]@row <> "", TODAY() - [Request Date]@row))

Did that work/help?

I hope that helps!

Have a fantastic weekend & Happy New Year!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

adegeus101536

Thank you both so much! I think I was looking at formulas too much and my brain turned to mush. Mark's second formula worked. I truly appreciate the help!

Andrée Starå

@adegeus101536

Excellent!

You're more than welcome!

Mark Cronk

Happy to help. Thank you for contributing to the Community.

Mark

adegeus101536

Me again! I am now trying to get another column to auto-populate a date. If the ticket is closed, I want the date to populate in the ticket closed date. Our guys seem to be forgetting to fill that in which means that formula you guys helped me with before just keeps calculating. For example, CJ changes his ticket status to work is completed, I would like the ticket closed date to populate to when he changed that. Is that possible? I thought an if function would work but again, I am struggling. Maybe I just need a vacation. This is what I have been playing with but again... unparseable:

=IF(Ticket Status = "work is completed", DATE(),"")

Paul Newcome

Smartsheet now has an automation that will capture a date stamp triggered when a specific action occurs. Sounds like a perfect fit for this case.

Andrée Starå

Hi @adegeus101536

You could use the new Record a date feature.

More info.

https://help.smartsheet.com/articles/2481986-set-the-current-date-with-record-a-date-action

Would that work/help?

I hope that helps!

Be safe and have a fantastic day!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

adegeus101536

I spent hours making notifications for this sheet yesterday. How did I miss that?! Absolutely wonderful addition. Works like a charm. Thank you both so much for the quick response. Life savers!

Andrée Starå

@adegeus101536

Excellent!

You're more than welcome!

Easy to miss!