Count how many tickets have been completed before the due date

Awesomas

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

Awesomas

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.

Aya

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

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.