How to Create 15-Minute Time Intervals in Excel

Time intervals in Excel are lists like 9:00, 9:15, 9:30: the backbone of schedules, booking sheets and call reports. You can make them with the fill handle, with a simple +15 minutes formula, or with one SEQUENCE formula. This guide shows all three, then the reverse job: putting real timestamps into 15-minute slots and counting them. It is for anyone building a rota, a timetable or a report by time of day.

How Excel stores time#

Excel stores a time as a fraction of a day. Midnight is 0, noon is 0.5 and 9:00 is 0.375. One day has 1,440 minutes, so:

That is why adding 1/96 to a time moves it on by 15 minutes. If your list ever shows 0.385416… instead of 9:15, nothing is broken: the cell just needs a time format. Select it and press Ctrl+1 (Mac: ⌘1), then choose Time or type the custom format h:mm. Dates work the same way, as whole numbers, which the lesson on how Excel stores dates explains.

Method 1: Fill 15-minute intervals with the fill handle#

The quickest way needs no formula. Excel copies the pattern from two example cells:

1. Type 9:00 in A2 and 9:15 in A3.
2. Select both cells.
3. Drag the fill handle (the small square at the bottom-right of the selection) down as far as you need.

Excel continues 9:30, 9:45, 10:00 and so on. Use 9:00 and 9:30 for half-hour steps, or 9:00 and 9:05 for five-minute steps. The guide to AutoFill series covers other patterns, and you can practise Fill Down and Fill Right in a live sheet.

For an exact end time, use the Series dialog instead. Type 9:00 in A2, select it, then go to Home > Fill > Series. Choose Columns, type 0:15 as the Step value and 17:00 as the Stop value, and click OK.

Method 2: Add 15 minutes with a formula#

A formula makes the start time easy to change. Type the first time in A2 and this in A3:

=A2+TIME(0,15,0)

Fill it down. TIME takes hours, minutes and seconds, so TIME(0,15,0) is 15 minutes. =A2+15/1440 or =A2+"0:15" do the same job. Now change A2 to 8:30 and the whole schedule moves.

To make the step adjustable, put the interval in a cell, such as D1 = 0:15, and use =A2+$D$1. The $ signs keep D1 fixed as you fill. The guide on adding hours and minutes to a time shows more examples.

Method 3: Build the whole list with SEQUENCE#

In Excel 2021 and Microsoft 365, SEQUENCE creates the whole list in one formula. Its arguments are rows, columns, start and step:

=SEQUENCE(32,1,TIME(9,0,0),TIME(0,15,0))

This spills 32 times, from 9:00 to 16:45. Format the spill range as h:mm. For a full day in 15-minute slots, use =SEQUENCE(96,1,0,1/96): 96 rows, starting at midnight. How SEQUENCE spills, and how to fix a #SPILL! error, is in the SEQUENCE function guide.

SEQUENCE is also more exact than adding 15 minutes over and over. Each repeated addition can carry a tiny rounding error, which can matter later when you compare times.

How to group timestamps into 15-minute intervals#

To put real timestamps into slots, round each one down to the start of its interval with FLOOR:

=FLOOR(A2,TIME(0,15,0))

A call at 9:38 goes into the 9:30 slot, and one at 9:46 into 9:45. FLOOR.MATH works the same way: =FLOOR.MATH(A2,TIME(0,15,0)). To round to the nearest quarter hour instead, see how to round time to the nearest 15 minutes.

If your times arrived as text, which they often do from exports, wrap them in VALUE first: =FLOOR(VALUE(A2),15/1440). That is the formula in the exercise on this page.

Because times are fractions, a time exactly on a boundary, like 9:45, can very rarely land in the slot before. If that matters, work in whole minutes:

=FLOOR(ROUND(A2*1440,0),15)/1440

How to count events in each interval#

To count how many calls fall in each slot, list the slot starts in C2:C5 (using any method above) and use COUNTIFS with two conditions. The call times are in A2:A9:

In D2, type this and fill down to D5:

=COUNTIFS($A$2:$A$9,">="&C2,$A$2:$A$9,"<"&C2+TIME(0,15,0))

The first condition keeps times at or after the slot start, and the second keeps times before the next slot. Using >= and < means a call at exactly 9:15 is counted once, in the 9:15 slot. For a readable label such as "9:00–9:15", join two TEXT results: =TEXT(C2,"h:mm")&"–"&TEXT(C2+TIME(0,15,0),"h:mm").

Pivot tables can group times by hour, but not into 15-minute buckets. Add a FLOOR helper column and use that column in the pivot instead.

Common problems with time intervals#

These are the usual reasons a time list misbehaves:

1. Decimals instead of times. Format the cells as h:mm. The values are right.
2. Times stored as text. Text times sit on the left of the cell and don't add up. Convert them with VALUE.
3. Lists that pass midnight. After 23:45 the next time is 0:00 of the next day. Format with [h]:mm for running totals over 24 hours; see how to calculate a time difference.
4. Date and time together. FLOOR works on full timestamps too. To drop the date and keep only the time, use =MOD(A2,1).

Practise it#

Time intervals rest on two basics: how Excel stores dates and times as numbers, and how fill tools repeat a pattern. Practise how Excel stores dates, then practise Fill Down and Fill Right. Each lesson runs in a live spreadsheet and checks your work instantly. Then try the exercise on this page to put a call into its 15-minute slot.