Time in range calculation: TIR from CGM data in Excel

The time in range formula

TIR (%) = readings from 70 to 180 mg/dL ÷ all valid readings × 100

Because a continuous glucose monitor (CGM) takes readings at fixed intervals, usually every 5 minutes, the share of readings equals the share of time. One day has 1,440 minutes, so a 5-minute sensor should deliver 288 readings. TIR × 24 turns the percentage into hours: 70% is 16.8 hours a day.

The example uses one reading per hour so that the whole day fits on screen. With real 5-minute data the formula is the same, only the range is longer.

Worked example: one day of readings, five bands

One synthetic glucose reading per hour (yellow inputs). Column C labels each reading, and rows 27 to 32 calculate the time in each band with COUNTIF and COUNTIFS.

Worked example: one day of readings, five bands

	A	B	C
1	Time	Glucose (mg/dL)	Band
2	00:00	112	In range
3	01:00	98	In range
4	02:00	84	In range
5	03:00	66	Low
6	04:00	52	Very low
7	05:00	75	In range
8	06:00	104	In range
9	07:00	138	In range
10	08:00	196	High
11	09:00	232	High
12	10:00	174	In range
13	11:00	142	In range
14	12:00	128	In range
15	13:00	188	High
16	14:00	214	High
17	15:00	162	In range
18	16:00	131	In range
19	17:00	119	In range
20	18:00	156	In range
21	19:00	205	High
22	20:00	262	Very high
23	21:00	180	In range
24	22:00	147	In range
25	23:00	70	In range
26
27	Time in range (70–180 mg/dL)		66.7%
28	Time below range (<70)		8.3%
29	Time below 54 (level 2)		4.2%
30	Time above range (>180)		25.0%
31	Time above 250 (level 2)		4.2%
32	Hours in range per day		16.0

Synthetic readings of a fictional person, one per hour for readability. Educational only, not medical advice: glucose targets are set individually with a qualified clinician.

In range, below and above: the five bands

The 2019 International Consensus on Time in Range reports five bands next to each other:

• Time below 54 mg/dL (level 2 low): =COUNTIF(B2:B25,"<54")/COUNT(B2:B25) = 4.2%
• Time below 70 mg/dL (all lows, including those below 54): 8.3%
• Time in range 70–180 mg/dL: 66.7%
• Time above 180 mg/dL (all highs, including those above 250): 25.0%
• Time above 250 mg/dL (level 2 high): 4.2%

The bands overlap on purpose: below 70 already contains below 54. So below 70 + in range + above 180 = 8.3% + 66.7% + 25.0% = 100%, which is a useful check. Do not add the level 2 bands on top.

Inclusive limits matter

A reading of exactly 70 or exactly 180 is in range. That is why the formula uses ">=70" and "<=180", while time below range uses a strict "<70" and time above range a strict ">180". The example day contains a 70 at 23:00 and a 180 at 21:00 to test exactly this. Use > and < in the TIR formula and the result drops to 58.3%, while the three bands no longer add up to 100%.

The general consensus targets

For most adults with type 1 or type 2 diabetes, the consensus suggests these general targets: TIR above 70%, below 70 under 4%, below 54 under 1%, above 180 under 25% and above 250 under 5%. Older or high-risk adults and pregnancy have different targets, and a care team may set individual ones.

Against the general targets, the example day misses on TIR (66.7%) and on both low bands (8.3% and 4.2%), and sits right at the limit above 180 (25.0%, not under 25%). One day is not enough to judge, though. The consensus recommends looking at 14 days with at least 70% of the expected readings, which is a data sufficiency check before any metric is reported.

mmol/L, mean glucose and GMI

With readings in mmol/L, the band is 3.9 to 10.0 and the formula becomes =COUNTIFS(B2:B25,">=3.9",B2:B25,"<=10")/COUNT(B2:B25). To convert, divide mg/dL by 18 (more precisely 18.016).

TIR usually appears next to two more numbers: the mean glucose and the glucose management indicator, GMI (%) = 3.31 + 0.02392 × mean glucose in mg/dL, which estimates an HbA1c-like value from CGM data. Variability is measured with the coefficient of variation, standard deviation ÷ mean, where a value of 36% or less is generally considered stable. Counting how often a reading falls into a band is the same idea as building a histogram in Excel.

Common mistakes

• Strict limits in the TIR formula. ">70" and "<180" drop the readings of exactly 70 and 180. Here TIR would fall from 66.7% to 58.3%.
• Adding the level 2 bands twice. Below 70 already includes below 54, and above 180 includes above 250. Only below 70 + in range + above 180 add up to 100%.
• Dividing by the expected readings instead of the actual ones. TIR uses COUNT of the valid readings. The expected 288 a day belongs to the data sufficiency check.
• Mixing units. A band of 70–180 applied to mmol/L readings puts every value below range. Use 3.9–10.0 for mmol/L.
• Judging from one day. One day is a snapshot. The consensus metrics are meant for about 14 days with at least 70% of the expected data.
• Counting text or error values. Sensor exports sometimes contain "LOW", "HIGH" or blanks. COUNT ignores text, so decide how to treat those entries before calculating.