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?

Best Answer

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭
    edited 01/09/21 Answer ✓

    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)



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