How to round time to the nearest 15 minutes
✓ Verified in LibreOffice 25.8.7.3 ✓ Verified in Google Sheets (2026-08-30)Snap clock-in/clock-out or log times to the nearest quarter hour for billing and timesheets.
The formula
| App | Formula | Notes |
|---|---|---|
| Excel (desktop) | =MROUND(A2,TIME(0,15,0)) | CEILING/FLOOR with TIME(0,15,0) round strictly up or down instead. |
| Google Sheets | =MROUND(A2,TIME(0,15,0)) | Identical. |
| LibreOffice Calc | =MROUND(A2,TIME(0,15,0)) | Identical. |
How it works
Times are day-fractions, so MROUND with a 15-minute step (TIME(0,15,0)) snaps to the nearest quarter hour: 9:07 is closer to 9:00 than 9:15, so it rounds down; 9:08 would round up. Billing that always rounds in one direction uses CEILING (up) or FLOOR (down) with the same step — and the step is arbitrary: TIME(0,6,0) gives tenth-of-an-hour rounding.
Verified, not just documented
We ran =TEXT(MROUND(TIME(9,7,0),TIME(0,15,0)),"H:MM") in LibreOffice 25.8.7.3 (headless, with forced recalculation) and it returned 9:00 — exactly the expected result. We then ran the same formulas in Google Sheets, executed 2026-08-30: a formula-only workbook goes into Google Drive, which converts it to a Sheet and recalculates every formula with Google’s own engine, and comes back out as .xlsx carrying the values Google computed. It returned 9:00 for the worked example, the same value LibreOffice produced. Both engines’ numbers on this page are executed results. The Excel formula follows Microsoft’s official documented syntax — we do not run desktop Excel.
Functions used
MROUND · TIME — see full Excel, Google Sheets & LibreOffice compatibility for each.