Urgent Care KPI Dashboard: Wait Times and Visits per Hour With AI

An urgent care dashboard should show door-to-provider time (median and 90th percentile), the left-without-being-seen rate, visits per hour by hour of day, total visits and payer mix, with a length-of-stay figure as a sixth. AI can build the sheet and write weekly commentary from a visit export once you have removed anything that identifies a patient.

The restraint is the hard part. Urgent care systems export dozens of fields, and the temptation is to chart all of them. A dashboard works when it answers the questions a manager asks on Monday morning: did patients wait longer than usual, did more of them walk out, which hours were overloaded, and did the mix of payers change what we earn per visit? Everything below serves those four questions: a stripped export, formulas you can check by hand, and a prompt for the weekly note.

Follow me on Instagram@sagnikteaches

Define each KPI before you chart it

Two clinics can both report "wait time" and mean different things. Write the definition next to each number, and use the definitions your clinical lead and your electronic record system already use. A working set:

Connect on LinkedInSagnik Bhattacharya

If your record system defines a measure differently from the table, use its definition, so the dashboard matches what your leaders already see in reports. Where you are comparing with a sister clinic, confirm they use the same definitions before comparing numbers.

Subscribe on YouTube@sagnikteaches

Get the export, and make it safe before AI sees it

Ask your system administrator or vendor for a visit-level export for the last 8 to 12 weeks, as a CSV, with only these columns:

visit_id (a random code, not the medical record number)
arrival_time
roomed_time
provider_time
discharge_time
disposition          (discharged, transferred, LWBS, other)
payer_class          (commercial, public, self-pay, occupational, other)
acuity               (optional: low, medium, high)
site                 (if you run more than one)

Leave out names, dates of birth, addresses, phone numbers, medical record numbers, diagnoses and free-text notes. Dashboards need times, categories and counts. If a timestamp could identify someone in a very small clinic (a single visit at 3 am), the safest approach is to work in your own spreadsheet and ask AI only for formulas and commentary on aggregated numbers, not the raw rows. The clinic data and consent checklist lists the questions to settle first, and your compliance lead should confirm what you may share with any tool before you start.

For a messy export, such as dates in two formats or blank discharge times, the older clean messy data tutorial covers the standard fixes, and a spreadsheet assistant can help with them on the stripped file.

Build the sheet in four tabs

1. Data: the export, one row per visit, with added helper columns.
2. Calc: weekly numbers from the data using formulas.
3. Dashboard: the six KPIs and three charts, nothing else.
4. Notes: definitions, the date of the last export, and who owns each KPI.

On the Data tab, add helper columns. With arrival in column B, provider time in D, discharge in E and disposition in F (data in rows 2 onward), the formulas look like this:

G2  Door-to-provider (min):  =IF(OR(B2="",D2=""),"",(D2-B2)*1440)
H2  Length of stay (min):    =IF(OR(B2="",E2=""),"",(E2-B2)*1440)
I2  Arrival hour:            =HOUR(B2)
J2  Week start (Monday):     =INT(B2)-WEEKDAY(B2,3)
K2  LWBS flag:               =IF(F2="LWBS",1,0)
L2  Weekday (1=Mon, 7=Sun):  =WEEKDAY(B2,2)

Multiplying a time difference by 1,440 converts days into minutes, which is how spreadsheets store time. In WEEKDAY, type 3 numbers Monday as 0 and type 2 numbers Monday as 1, in both Excel and Google Sheets, so the week-start column always lands on a Monday. Blank cells stay blank rather than becoming zero, which would drag your median down. Check a few rows by hand: a visit arriving at 10:05 and seen at 10:47 should show 42.

On the Calc tab, for the week starting in cell A2, the weekly figures are:

Visits:          =COUNTIFS(Data!J:J,A2)
LWBS rate:       =SUMIFS(Data!K:K,Data!J:J,A2)/COUNTIFS(Data!J:J,A2)
Median D2P:      =MEDIAN(IF(Data!J2:J5000=A2,IF(Data!G2:G5000<>"",Data!G2:G5000)))
90th pct D2P:    =PERCENTILE(IF(Data!J2:J5000=A2,IF(Data!G2:G5000<>"",Data!G2:G5000)),0.9)

The last two are array formulas. Microsoft's guidance is that legacy array formulas need Ctrl+Shift+Enter, while dynamic-array Excel (Microsoft 365, Excel 2021 and 2024) takes them with a plain Enter. In Google Sheets, press Ctrl+Shift+Enter while editing and Sheets wraps the formula in ARRAYFORMULA for you. Where FILTER is available, it reads more plainly and skips the nested IFs:

Google Sheets:  =MEDIAN(FILTER(Data!G2:G5000, Data!J2:J5000=A2, Data!G2:G5000<>""))
Excel 365:      =MEDIAN(FILTER(Data!G2:G5000, (Data!J2:J5000=A2)*(Data!G2:G5000<>"")))

Note the difference: Sheets takes each condition as its own argument, while Excel multiplies the conditions together. Whichever version you use, test the result on a week you can compute by hand, because a wrong range end (row 5000 when the data now runs to row 6200) fails silently.

Visits per hour by hour of day is best done with a pivot table: rows are arrival hour (0 to 23), columns are weekday, values are count of visit_id, divided by the number of weeks in your data to give an average. If you would rather use formulas, put the hours 0 to 23 down column A and the weekday numbers 1 to 7 across row 1, then =COUNTIFS(Data!$I:$I,$A2,Data!$L:$L,B$1,Data!$J:$J,">="&start,Data!$J:$J,"<="&end)/weeks gives the average arrivals for that hour on that weekday. Leave out the weekday condition and you get a whole week's arrivals in that hour, seven days added together, which is the mistake that makes an evening look three times busier than it is.

A worked example with numbers

Take an illustrative single-site clinic open 8 am to 8 pm, with about 100 visits on a typical weekday. For one week, the Calc tab gives (all figures invented for the example):

Check the arithmetic the way a manager would. 3.4% of 742 visits is about 25 patients who left unseen; 2.1% of 701 would be about 15. Ten more people walked out than usual, which has a visible revenue and reputation cost. The by-hour chart then shows where: Monday and Tuesday between 5 pm and 7 pm averaged 18 arrivals an hour against a typical 12, and the staffing sheet had two providers on until 6 pm and one after. That points at the rota, which is the next job for the urgent care staffing and forecast tutorial.

The weekly commentary prompt, with a sample reply

Paste the aggregated table only, no visit rows, and ask for a note a manager can read in a minute:

Here are this week's aggregated numbers for an urgent care clinic, compared with the average of
the previous eight weeks: [table]. Also the visits-per-hour table by weekday: [table].
Write a commentary of under 150 words for the operations manager.
Include: the two biggest changes, the hours or days where demand was highest, and one question
for the clinical lead to answer. Do not suggest clinical changes. Do not state causes as facts:
use "may be" where the data cannot show why. Do not invent numbers.

Visits rose about 6% and the median door-to-provider time rose from 22 to 27 minutes. The 90th percentile moved from 55 to 71 minutes, so the longest waits grew most. The left-without-being-seen rate rose from 2.1% to 3.4%, roughly ten more patients than usual. Arrivals peaked at about 18 an hour on Monday and Tuesday between 5 and 7 pm, against a usual 12. Longer waits at those times may explain the walk-outs. Question for the clinical lead: was provider cover reduced on those evenings, or were there unusually complex cases?

Read the sample critically. "May explain" is the right hedge; the data shows a coincidence, not a cause. A less careful output might say "understaffing caused the increase", which no table supports. If the assistant states a cause, rerun the prompt with the "no causes as facts" line stressed, or edit it yourself. Also check each number against the sheet, since a model can swap two figures in a sentence. For narrative on comparable operating dashboards, see the medical practice KPI dashboard tutorial.

Payer mix: a quick sum on what the share means

A change of two points in payer mix looks trivial until you price it. Take the illustrative clinic above: 742 visits, and suppose your billing team's own figures give an average net collection of $140 for a commercial visit, $95 for a public-programme visit and $70 for self-pay (invented numbers; use your finance team's). If last week was 58% commercial, 27% public and 15% self-pay, the weighted value per visit is 0.58 × 140 + 0.27 × 95 + 0.15 × 70 = 81.2 + 25.65 + 10.5 = $117.35, or about $87,070 across 742 visits. At the previous 61% commercial, 26% public and 13% self-pay, it is 85.4 + 24.7 + 9.1 = $119.20, about $88,450 across the same visits. A two-to-three point shift is therefore worth about $1,400 in a week, enough to notice and not enough to panic over.

The point of the sum is to see whether a mix change is large enough to matter, and the finance figures must come from your own billing data, not an AI estimate. Ask the assistant to build the weighted-value formula from your table of collection figures, then check one week by hand as above.

A bad dashboard and the good version

A common starting point is a page with eighteen tiles: every wait in every unit, an average of everything and a colourful chart for each. Here is the before and after for a manager who asked an assistant to "build a dashboard from this CSV":

• Before: 18 tiles, averages only, no baseline, no definition of arrival time, three charts that all showed volume, and a payer pie with nine slices.
• After: six KPIs with a baseline and a week-on-week arrow, median and 90th percentile side by side, a by-hour column chart, one line for LWBS with its limit, a payer table with five rows, and a Notes tab with definitions.

The after version has fewer elements and is more useful, because every number has a comparison and an owner. When you ask an assistant to build or restyle the page, give it that brief: six KPIs, three charts, a baseline for each, and nothing more.

Digging into walk-outs without patient details

When the LWBS rate climbs, the question is who left and when, and the answer lies in aggregates. Add a helper column for arrival hour and one for weekday, then count LWBS by hour. Illustrative result: of the 25 walk-outs, 14 arrived between 5 pm and 7 pm, and 17 left after waiting more than 45 minutes. Together they suggest a capacity problem in two evening hours rather than a general slowdown. Ask the assistant to propose three explanations that the data could support or reject, such as provider cover, a registration bottleneck or a cluster of higher-acuity arrivals, and which column you would check for each. Then check them with the people who were there. The numbers tell you where to look; staff tell you what happened.

Three charts, no more

• Median and 90th-percentile door-to-provider time by week. A line chart with two lines over the last 12 weeks, and the baseline marked.
• Visits per hour by arrival hour. A column chart for the typical weekday, with the staffed capacity as a line across it if you have that figure.
• LWBS rate by week. A line chart with a threshold line at the level your leaders have agreed.

Payer mix is better as a small table than a pie chart: share by class for the last week and last eight weeks, side by side. People read a table of five numbers faster than they compare slices. Add colour only where a number has crossed its agreed limit. A dashboard with red everywhere teaches staff to ignore red.

Setting targets without borrowing them

An assistant will happily quote a "typical" door-to-provider time or an "acceptable" LWBS rate, and those figures may come from different settings or years. Do not adopt them. Use your own history: take the eight-week median for each KPI as the baseline, and ask your clinical lead to propose a target, such as a modest reduction in the 90th percentile, along with a stated review date. Your payer contracts or quality programmes may also set required measures, and those definitions win over anything on the dashboard. Write the target next to each KPI on the Notes tab, with who set it and when.

Where AI-built dashboards go wrong

1. Timestamps in different time zones or formats. A daylight-saving change can shift a week of arrivals by an hour. Check the hour chart for a suspicious dip or spike at the clock change.
2. Open visits. Visits without a discharge time inflate or break length-of-stay. Exclude them and count how many you excluded.
3. LWBS defined inconsistently. Some systems record patients who leave after triage but before a provider as LWBS, others use a different code. Confirm the disposition values with the person who owns the system.
4. A median over too few rows. On a quiet site, one weekend afternoon may have five visits. Do not read a trend into small counts.
5. A dashboard nobody owns. Name one person who refreshes it every Monday and one who reads it with the clinical lead.

A weekly routine that takes twenty minutes

1. Export last week's visits (or let the system send it on a schedule) and paste it under the existing rows on the Data tab.
2. Check the helper columns have filled down and spot-check three rows by hand.
3. Read the Dashboard tab; note anything outside its agreed limit.
4. Copy the aggregated table to the commentary prompt and read the reply against the sheet.
5. Send the commentary with the three charts to the clinical lead; write the one question for the Monday huddle.

Where you have several sites, add the site column to every formula and keep one dashboard per site, plus a summary tab. Do not compare sites until you have confirmed they define arrival and provider time the same way. The KPI dashboard without a data team tutorial covers the habits that keep a sheet like this alive once the first enthusiasm wears off.

Sharing it with staff without creating a blame sheet

A dashboard that names no cause and no person is easier to discuss honestly. Show clinic-level numbers, not provider league tables, unless your clinical lead has agreed that approach and the staff know about it. Open the Monday huddle with the one question from the commentary, give people two minutes to answer from their own experience, and write the answer on the Notes tab beside the week it explains. After a few months those notes become the most valuable part of the file, because they link each blip in the numbers to a real event: a sick provider, a system outage, a school holiday week or a local outbreak. When the AI commentary later sees a similar pattern, you can paste in those notes as context, and its questions improve.

Checking the dashboard is right

Before anyone relies on it, run three tests. First, pick one day and count the visits by hand in your record system; the dashboard's count must match, or you have a filter or date problem. Second, take ten visits and calculate their door-to-provider time with a calculator; the helper column must match. Third, change nothing and compare last week's numbers with a report your system already produces; if the median wait differs by more than a minute or two, find out why before the dashboard goes into a meeting. These checks take under an hour and are the difference between a tool that guides decisions and one that starts arguments about whose number is right.

When the numbers are sound and the long waits are real, the next step is acting on them: the wait-time texts and hand-offs tutorial covers how to keep patients informed while the queue is long. If length of stay is climbing while door-to-provider time is flat, look at what happens after the provider sees the patient, documentation included, before adding staff at the front.

Questions urgent care managers ask about dashboards

How many KPIs should an urgent care dashboard have?

Five to eight on the first page. Door-to-provider time, left-without-being-seen rate, visits per hour by hour of day, total visits, payer mix and a length-of-stay figure cover most decisions about staffing and flow. More than that and nobody looks at the page. Put detail on a second tab for the person who wants to investigate.

Should I use the average or the median wait?

Show both, but lead with the median and a 90th-percentile figure. An average is dragged up by a few very long waits and hides how a typical patient was treated, while the 90th percentile shows how bad the long waits were. Report the figures the way your clinical lead and your staff will read them.

Can I upload patient visit data to an AI tool?

Only after removing everything that identifies a person, and only in a tool whose business terms and your health-data obligations permit it. A dashboard needs timestamps, categories and counts, not names, dates of birth or notes. Strip those columns before the file leaves your system, and check with whoever owns compliance.

Who should decide the targets on the dashboard?

Your clinical and operations leads, from your own history and your commitments to patients and payers. Do not take a target from an AI answer or a table found online. Start by showing the last eight weeks as the baseline, then agree a modest improvement for each KPI and review it each quarter.

Further reads

• How a Clinic Can Use AI to Cut Missed Appointments — Cut no-shows, the other half of keeping a schedule full.
• How a Small Clinic Can Use AI at the Front Desk Safely — Front-desk steps that shorten check-in and the first wait.
• 10 Admin Tasks a Small Clinic Can Hand to AI This Month — Ten admin tasks a clinic can hand to AI this month.
• Law Firm KPI Dashboard: A Monthly Partner Pack Built With AI — A monthly KPI pack built the same way, in a different sector.
• How to Use Microsoft Copilot for Data Analysis in Excel — Ask Copilot questions of your visit data in Excel.
• Best AI Scribe for Urgent Care: Fast-Visit Notes and Coding Checks — Faster visit notes, one cause of slow throughput.
• AI Tools and AI Development: The Complete 2026 Guide — the AI hub, including every tutorial in the AI-for-business series.

Sources: Microsoft Excel and Google Sheets help pages for WEEKDAY, FILTER, ARRAYFORMULA and array formulas (read November 2026); the reader's own clinical lead, compliance owner and payer contracts for definitions, targets and data rules.