Round Time to the Nearest 15 Minutes in Excel

To round time in Excel to the nearest 15 minutes, use =MROUND(A2,"0:15") and format the cell as h:mm. So 8:53 becomes 9:00 and 8:38 becomes 8:45. If you need a version built from basic functions, =ROUND(A2*96,0)/96 gives the same result. This guide explains why both work, how to always round up or down, and how to round hours worked for a timesheet.

How does Excel store time?#

Excel stores a time as a fraction of a day. Midnight is 0, noon is 0.5, and 9:00 AM is 0.375 (9 divided by 24). A date is a whole number and the time is the part after the decimal point.

That's why rounding time needs a little arithmetic. A day has 24 hours × 60 minutes = 1,440 minutes, so:

The lesson on how Excel stores dates shows the same idea for whole dates.

How to round time to the nearest 15 minutes with MROUND#

MROUND rounds a number to the nearest multiple of another number. Give it the interval as a time in quotes:

=MROUND(A2,"0:15")

Excel reads "0:15" as 15 minutes, the same as 1/96. Here are four clock-in times in A2:A5:

MROUND also accepts the interval as a plain number, so =MROUND(A2,1/96) gives the same results. Some people find the fraction clearer, because it shows exactly what Excel is doing.

Format the results as h:mm: press Ctrl+1 (Mac: ⌘1), choose Custom, and type h:mm. Without a time format, the cell shows a decimal such as 0.375. MROUND is a standard function in Excel 2007 and later. The guide to rounding to the nearest 5, 10 or 0.5 shows MROUND with ordinary numbers.

How to round time with ROUND (works everywhere)#

You can do the same thing with ROUND, which every spreadsheet tool has. Multiply the time by 96 to count quarter hours, round to a whole number, then divide by 96 to turn it back into a time:

=ROUND(A2*96,0)/96

For 8:53, A2×96 is about 35.53 quarter hours. ROUND makes it 36, and 36/96 is 0.375, which is 9:00. Change 96 to round to another interval: 48 for half hours, 288 for 5 minutes, 24 for whole hours.

This version is also handy when your times arrive as text, for example from a badge reader export. Wrap the cell in VALUE first: =ROUND(VALUE(A2)*96,0)/96. The ROUND function guide covers its other uses, and the guide to ROUND, ROUNDUP and ROUNDDOWN compares the three.

How to always round time up or down#

Sometimes "nearest" isn't the rule you want. For example, a parking charge might always round up to the next quarter hour. Use CEILING to round up and FLOOR to round down:

=CEILING(A2,"0:15")
=FLOOR(A2,"0:15")

A time that is already on a quarter hour stays the same with both. In Excel 2013 and later, CEILING.MATH and FLOOR.MATH work the same way for times. The comparison of CEILING vs FLOOR explains the difference with negative numbers.

If you round staff clock-in and clock-out times for pay, check your employer's policy and the rules where you work. Rounding to the nearest interval treats both sides evenly. Rounding in only one direction does not.

How to round hours worked to the nearest quarter hour#

For pay, you usually want hours as a number, such as 8.25, not a clock time. Subtract the start from the end, multiply by 24 to get hours, then round to quarters:

=ROUND((C2-B2)*24*4,0)/4

For a shift from 8:53 to 17:11, the difference is 8 hours 18 minutes, or 8.3 hours. Multiplying by 4 gives 33.2, ROUND makes it 33, and 33/4 is 8.25. Leave this cell in a number format, not h:mm. The guide to calculating time differences covers shifts that pass midnight, and the timesheet guide puts it all into a weekly sheet.

Common mistakes when rounding time#

These are the problems people hit most often:

• The result shows a decimal. That is the right value in the wrong format. Apply h:mm.
• The interval isn't in quotes. =MROUND(A2,0:15) is a syntax error. Write "0:15" or use 1/96.
• The times are text. Times pasted from another system may be text, and math on them can fail. Convert them with VALUE first.
• Formatting instead of rounding. A format like h:mm hides the seconds but doesn't change the value. Use a rounding formula when the value itself must change.
• Totals over 24 hours look too small. A weekly total of 41 hours 30 minutes shows as 17:30 in h:mm format, because the format starts again after each full day. Use the custom format [h]:mm for totals.
• Rounding the clock times, then subtracting. Rounding each end and rounding the total can give different hours. Pick one rule and use it for every row.

Practise it#

Rounding time is just rounding numbers once you know that a day is 1. Practise ROUND, ROUNDUP and ROUNDDOWN in a live spreadsheet: you type each formula and it checks it instantly. Then practise how Excel stores dates so time fractions feel natural. Or try the clock-in exercise on this page first.