Round times or durations to 15-minute increments

Represent time in quarter-hour units

Because 24 hours * 4 quarter-hours = 96 intervals per day, multiplying any spreadsheet time serial by 96 converts it into quarter-hour units (08:07 -> 32.467; 17:08 -> 68.533).

ROUND(Time * 96, 0) chooses the nearest quarter-hour. For whole-minute inputs, offsets 00–07 round down and 08–14 round up; with seconds included the midpoint is 7 minutes 30 seconds. CEILING.MATH rounds a positive duration upward when that is the chosen billing policy.

Rounded endpoint hours and duration ceiling

Subtracting integer quarter-hour counts and dividing by 4 gives exact decimal hours (9.25).

=(ROUND(B2 * 96, 0) - ROUND(A2 * 96, 0)) / 4

Billable duration rounded up to next 15 minutes

A 1-hour 2-minute call (62 minutes) bills as 1.25 hours under CEILING.MATH.

=CEILING.MATH((B2 - A2) * 96, 1) / 4

Inputs and interpretation

These are arithmetic policies, not a determination that payroll or billing rounding is permitted. Use the rules applicable to the contract and jurisdiction. Include dates for overnight or multi-day work and require end >= start. Breaks, time zones and daylight-saving changes are not accounted for. Rounding both endpoints differs from rounding the elapsed duration; floating-point boundary checks are still needed.

Further reading

• Microsoft Support — TIME function