# How to calculate days a ticket is open/closed

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?

Tags:

• ✭✭✭✭✭✭

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

I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

• ✭✭✭✭✭✭

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)

Work?

Mark

I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

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

• ✭✭✭✭✭✭

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

I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

• ✭✭✭✭✭✭

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

SMARTSHEET EXPERT CONSULTANT & PARTNER

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

W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

• 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!

• ✭✭✭✭✭✭

Excellent!

You're more than welcome!

SMARTSHEET EXPERT CONSULTANT & PARTNER

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

W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

• ✭✭✭✭✭✭

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

Mark

I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

• 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(),"")

• ✭✭✭✭✭✭

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.

• ✭✭✭✭✭✭

You could use the new Record a date feature.

Would that work/help?

I hope that helps!

Be safe and have a fantastic day!

Best,

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

SMARTSHEET EXPERT CONSULTANT & PARTNER

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

W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

• 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!

• ✭✭✭✭✭✭

Excellent!

You're more than welcome!

Easy to miss!

SMARTSHEET EXPERT CONSULTANT & PARTNER

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

W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.