Library › Coding AI › Customer support teams

Measure first-reply time across two time zones

Practice: Giving the right context. About 5 minutes.

About this exercise

Write one Excel formula for Tickets!E2 (filled down) that gives hours to first reply, rounded to 1 decimal. Watch out: Created (C) comes from the web form in UTC, but First reply (D) comes from the helpdesk in Pacific time, which is UTC-8 this winter, so convert D to UTC (add 8 hours) before subtracting. Tickets with no reply yet must stay blank. One formula in one code block, then under 25 words outside the code.

Common mistakes when you ask AI for this

  • Not mentioning the time zones. The AI subtracts the raw times, so some replies seem to happen before the ticket. Try: Add: "C is UTC, D is Pacific (UTC-8): add 8 hours to D first."
  • Leaving the units open. Excel subtracts dates in days, so the AI returns 0.31 instead of 7.5 hours. Try: Add: "Hours, rounded to 1 decimal."
  • Forgetting open tickets. An empty reply gives a big negative number. Try: Add: "No reply yet: leave it blank."
  • Getting a menu of options. The AI likes to offer two or three versions of the formula, so you're left to pick and test them yourself. Try: Add: "Give one formula only, in one code block, no alternatives."
  • Getting a tutorial. By default the AI explains every part of the code at length, which you then have to scroll past. Try: Add: "After the code, one line under 10 words. Nothing else."

What a strong prompt includes

  • Explains the time zones
  • Asks for hours
  • Handles open tickets
  • Asks for one answer only
  • Limits the explanation

Practice this now ↑

Related exercises