Count how many tickets have been completed before the due date

Options

Hi,

I'm trying to create a formula metric to count the number of instances that a ticket has been resolved before the due date has expired. I'm a bit lost how to use =COUNT(IF([Due Date]>[Resolved],1,"")). Sorry new to smartsheet appreciate any help.

Thanks

Best Answer

  • Awesomas
    Awesomas ✭✭
    Answer ✓
    Options

    Hi Aya

    Sorry for the late reply but I have it solved thanks to your help and have some other data that may be useful later. I created the column

    SLA Remaining like you suggested and that has

    =[Due Date]@row - Resolved@row in it.

    Then I created a Completed On time column that basically checks if SLA Remaining is <0 so a negative or not with and puts a yes or no

    =IF(NOT(ISBLANK(Resolved@row)), IF([SLA Remaining]@row < 0, "No", "Yes"))

    Then in my metric Tickets Resolved within SLA it counts the YESs in Completed on time

    =COUNTIF({Completed Ontime}, {Completed Ontime} = "Yes")

    Not very elegant but it has done the job. Thanks again for your help and pointing me in the right direction.

Answers

  • Aya
    Aya ✭✭✭
    Options

    HI Awesomas,


    You might need to create a formula column that would allow you to see the difference between the Due Date and Resolved Date:

    =[Due Date]@row-[Resolved]@row 
    

    Then, on your COUNTIFS formula you can use that formula column as one of your range and have something like this:

    =COUNTIFS({Ticket# Column},<>"", {Formula Column}, >0)
    


    Let me know if this helps or if you have questions.


    Cheers!

    -Aya

  • Awesomas
    Awesomas ✭✭
    Answer ✓
    Options

    Hi Aya

    Sorry for the late reply but I have it solved thanks to your help and have some other data that may be useful later. I created the column

    SLA Remaining like you suggested and that has

    =[Due Date]@row - Resolved@row in it.

    Then I created a Completed On time column that basically checks if SLA Remaining is <0 so a negative or not with and puts a yes or no

    =IF(NOT(ISBLANK(Resolved@row)), IF([SLA Remaining]@row < 0, "No", "Yes"))

    Then in my metric Tickets Resolved within SLA it counts the YESs in Completed on time

    =COUNTIF({Completed Ontime}, {Completed Ontime} = "Yes")

    Not very elegant but it has done the job. Thanks again for your help and pointing me in the right direction.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!