Cellwise Try it free

Excel guide · 4 min read

Round time to the nearest 15 minutes in Excel

Quick answerUse =MROUND(B2,"0:15") to round the time in B2 to the nearest quarter hour, so 7:53 becomes 8:00 and 8:08 becomes 8:15. Use =CEILING(B2,"0:15") to always round up and =FLOOR(B2,"0:15") to always round down. Format the result as a time if it shows as a decimal.

Sample data

Every result on this page was calculated from this sheet by the same spreadsheet engine the Cellwise app uses.

ABC
1EmployeeClock inClock out
2Rosa7:5316:08
3Dev8:0717:22
4Kim8:0816:37
5Sam7:4415:52
6Lee8:2217:07
7Ana8:0016:30

Download this sheet as an Excel practice file (.xlsx)

Nearest 15 minutes with MROUND

MROUND rounds a number to the nearest multiple of another number. For times, the multiple is 15 minutes, which you can write as "0:15": Excel turns the text into the time value 0.0104 (15 of the 1,440 minutes in a day). 15/1440 gives the same value and avoids the quotes.

The result is a plain number, so a new cell may show 0.333333 instead of 8:00. Press Ctrl+1 and choose a Time format, or copy the format from the clock-in column with the Format Painter.

=MROUND(B2,"0:15")

Result: 0.33333333333 shows as 8:00 with the custom h:mm format

Rosa clocked in at 7:53, seven minutes before 8:00, so it rounds to 8:00.

Where the halfway point falls

The nearest quarter hour flips at 7.5 minutes past each mark. Dev clocked in at 8:07, which rounds back to 8:00; Kim clocked in at 8:08, which rounds forward to 8:15. Clock systems that record whole minutes never land exactly on 7.5, so with MROUND minutes 1 to 7 round down and 8 to 14 round up. That matches what many payroll policies call the "7-minute rule".

Rounding rules for pay are set by employers and can be regulated where you work, so check which rule applies to you. The formulas here only do the arithmetic.

=MROUND(B4,"0:15")

Result: 0.34375 shows as 8:15 with the custom h:mm format

Kim's 8:08 rounds to 8:15. The same formula on Dev's 8:07 in B3 gives 8:00.

Always round up or down: CEILING and FLOOR

Some rules do not round to the nearest mark. A start time might count from the next quarter hour, and an end time from the previous one. CEILING always rounds up to the next multiple and FLOOR always rounds down. Both leave a time that is already on a quarter hour unchanged, so Ana's 8:00 stays 8:00.

Lee clocked in at 8:22. MROUND gives 8:15, CEILING gives 8:30 and FLOOR gives 8:15. In Excel 2010 and later, CEILING.MATH and FLOOR.MATH give the same results for positive values like times.

=CEILING(B6,"0:15")

Result: 0.35416666667 shows as 8:30 with the custom h:mm format

8:30: Lee's 8:22 goes up to the next quarter hour. =FLOOR(C6,"0:15") takes his 17:07 clock-out down to 17:00.

Rounded shift length in decimal hours

Payroll usually wants hours as a decimal, such as 8.25. Round each punch first, subtract, then multiply by 24 to turn the fraction of a day into hours.

Kim worked 8:08 to 16:37, which is 8 hours 29 minutes, or 8.48 hours unrounded. Rounding both punches gives 8:15 to 16:30, which is exactly 8.25 hours. Rounding Rosa's punches the same way gives 8.25 hours; using CEILING on her start and FLOOR on her end gives 8 hours.

=(MROUND(C4,"0:15")-MROUND(B4,"0:15"))*24

Result: 8.25

8.25 hours for Kim, from 8:15 to 16:30.

Round the punches or round the total?

These are different rules and give different answers. Rounding the total shift to the nearest quarter hour takes Kim's 8.48 hours to 8.5, while rounding each punch gave 8.25. Pick the one your policy describes and use it for everyone.

To round a decimal number of hours to the nearest quarter, multiply by 4, round, and divide by 4. Here the difference is multiplied by 96 (24 hours times 4 quarters) in one step. MROUND((C4-B4)*24,0.25) gives the same result.

=ROUND((C4-B4)*96,0)/4

Result: 8.5

8.5 hours: Kim's raw 8.48 hours rounded to the nearest quarter, compared with 8.25 from rounding each punch.

Without MROUND: ROUND with 96

A day has 96 quarter hours. Multiply a time by 96, round it to a whole number of quarters, and divide by 96 again. It gives the same result as MROUND and makes it easy to switch direction: ROUNDUP for always up, ROUNDDOWN for always down.

Change 96 to 48 for the nearest 30 minutes, 24 for the nearest hour, or 288 for the nearest 5 minutes. With MROUND, the same changes are "0:30", "1:00" and "0:05".

=ROUND(B3*96,0)/96

Result: 0.33333333333 shows as 8:00 with the custom h:mm format

Dev's 8:07 rounds to 8:00, matching MROUND.

Common mistakes

Writing 15 or "15" as the multiple

To Excel, 15 means 15 days, so =MROUND(B2,15) returns 0 (midnight). Use "0:15" or 15/1440.

The rounded time shows as 0.333333

MROUND returns a plain number. Format the cell as Time (Ctrl+1) or wrap it: =TEXT(MROUND(B2,"0:15"),"h:mm") for display only.

Multiplying the wrong way for decimal hours

A time difference times 24 gives hours. Forgetting the *24 leaves the fraction of a day, such as 0.34 for an 8.25-hour shift.

An overnight shift gives a negative number

Round both punches, then wrap the difference in MOD: =MOD(MROUND(C2,"0:15")-MROUND(B2,"0:15"),1)*24.

FAQ

How do I round time to the nearest 15 minutes in Excel?

Use =MROUND(A2,"0:15"), then format the cell as a time. 7:53 becomes 8:00 and 8:08 becomes 8:15.

How do I round time up to the next 15 minutes?

Use =CEILING(A2,"0:15"). It rounds 8:01 to 8:15 and leaves times already on a quarter hour, such as 8:00, unchanged. FLOOR does the opposite and always rounds down.

How do I round to the nearest 30 minutes or the nearest hour?

Change the multiple: =MROUND(A2,"0:30") for half hours and =MROUND(A2,"1:00") for whole hours. The same works with CEILING and FLOOR.

How do I round decimal hours to the nearest quarter hour?

Multiply by 4, round, then divide by 4: =ROUND(A2*4,0)/4 turns 8.48 into 8.5. MROUND(A2,0.25) gives the same answer.

Functions used

Related guides

Updated