How We Solved Ticket Creation vs Assignment Time Calculation in Excel

Background

A customer support team needed to track how much time it took between a ticket being created and being assigned to an agent. While the information was available, the data formatting prevented simple calculations.
We stepped in and leveraged Generative AI to solve it using Excel functions smartly.

Problem

Highlight the challenges:
Ticket times were stored as text, not real Date-Time values.
Presence of weekday names and extra commas made the data unreadable for Excel formulas.
Simple subtraction of times was resulting in errors.

Our Solution (in Simple Steps)

Step 1: Extracted only Date and Time, removing weekday names.
Step 2: Converted text into true Date-Time values.
Step 3: Subtracted the two times after conversion.
Step 4: Applied proper formatting (hh:mm:ss) to display the time difference beautifully.

Final Formula Used

=SUBSTITUTE(TRIM(MID(J2,FIND(",",J2)+1,LEN(J2))),",","")-SUBSTITUTE(TRIM(MID(I2,FIND(",",I2)+1,LEN(I2))),",","")

Key Takeaways

Always ensure date-time fields are truly recognized by Excel before calculations.
Cleaning text data is often the first step toward real analysis.
Even complex-looking problems can be solved with a few smart formulas.

Outcome

The customer support team could now calculate the exact time taken for each ticket assignment, helping them improve response efficiency and agent allocation strategies.

Call to Action

Facing data format issues in your organization? Contact us to learn how we can help you.