Time clocks and schedules often round to the nearest quarter hour. MROUND snaps any time to the nearest 15 minutes (or 5, 30, whatever you choose) — and CEILING/FLOOR force it up or down.
"0:15" is the multiple (15 min). MROUND picks the nearest one; 9:07 rounds down to 9:00, 9:08 rounds up to 9:15.
The example
Punch times rounded to the nearest quarter hour.
| A | B | |
|---|---|---|
| 1 | Actual | Nearest 0:15 |
| 2 | 9:07 AM | 9:00 AM |
| 3 | 9:08 AM | 9:15 AM |
| 4 | 5:52 PM | 5:45 PM |
The formula
Round to the nearest 15-minute mark:
How it works
MROUND rounds to the nearest multiple of a value:
- The 2nd argument is the multiple —
"0:15"is 15 minutes expressed as a time. Excel reads it as the fraction 15/1440 of a day. - MROUND finds the nearest multiple — below the midpoint rounds down, at or above rounds up.
- For other intervals, change the multiple:
"0:05"(5 min),"0:30"(half hour),"1:00"(whole hour). - You can also use the fraction directly:
=MROUND(A2, 15/1440)is identical to"0:15".
Always round up (or down)? Payroll often rounds clock-in up and clock-out down. Use =CEILING(A2, "0:15") to always round up and =FLOOR(A2, "0:15") to always round down.
Try it: interactive demo
Enter a time and an interval (minutes).
Variations
Always round up
CEILING never rounds down:
Always round down
FLOOR never rounds up:
Nearest 5 minutes
Change the multiple:
Pitfalls & errors
Quote the time multiple. Use "0:15" (text Excel reads as time) or the fraction 15/1440 — a bare 15 means 15 days.
MROUND vs CEILING/FLOOR. MROUND goes to the nearest mark; if your policy always rounds one direction, use CEILING or FLOOR instead.
Format as time. The result is a serial fraction; if it shows as a decimal, apply a time format to the cell.
Practice workbook
Frequently asked questions
How do I round time to the nearest 15 minutes in Excel?
How do I always round time up or down?
How do I round to a different interval?
Stop fighting formulas. Learn them in a day.
This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.
See the Formulas & Functions Class