How to round time to nearest hour/minute/second in Excel?
Round time to nearest hour/minute/second
Round time to nearest hour/minute/second
Rounding time values is common in workforce management timesheets, event tracking logs, or any scenario where meeting standard time intervals is helpful. For example, attendance logs may require rounding clock-in/out times to the nearest quarter hour, or data logging systems may need to snap timestamps to the closest minute or second. Excel offers several flexible approaches for these tasks.
Round time to nearest 1 hour
To round individual time values to the nearest hour (for example, changing 09:27:35 to 09:00:00 or 10:00:00 depending on which hour is closer), you can use Excel’s built-in functions. Select a cell next to your time entry and type the following formula:
=MROUND(A2,"1:00")After pressing Enter, click and drag the fill handle down to apply the formula to other necessary cells.
Parameter explanations and practical tips:
- The
MROUNDfunction rounds values to the nearest multiple; here it adjusts times to the closest whole hour. The "1:00" argument tells Excel to use an interval of one hour. - If your data is in a different cell, be sure to update
A2to match. - You must format result cells for time display to see the rounded hour correctly (right-click cells, select Format Cells, choose Time category, pick your desired time format, and click OK).
- If the formula returns a number (like 0.5), it's because the cell isn’t in "Time" format — adjust the format as described above.
You can also achieve this with alternative formulas:
-
=ROUND(A2/(1/24),0)*(1/24) -
=MROUND(A2,1/24)
These formulas also round a time value to the nearest hour. 1/24 represents one hour in Excel's time system (where 1 is a full day). Remember to use the right format for result cells.
If you need to round down to the previous whole hour (regardless of the number of minutes or seconds), use:
=FLOOR(A2,1/24)For all these formulas, the "1" can be replaced with another number to round to a different hour interval, such as 0.5 (30 minutes) or 2 (every 2 hours), depending on your needs.
Round time to nearest 15 minute
To round time to the nearest 15-minute interval, which is useful for payroll rounding, time tracking, or reporting, enter in your desired cell:
=ROUND(A2*(24*60/15),0)/(24*60/15)Then press Enter and use the autofill handle to apply to additional rows.
To ensure Excel displays the result as a time value, remember to set the cell format to Time (see above). You can also use a simpler, more readable formula with MROUND:
=MROUND(A2,15/60/24)This formula works for any rounding interval by changing 15 to another number — for example, use 5 for rounding to the nearest five minutes.
If always rounding down to the previous 15-minute increment is needed (for example, billing purposes), substitute with:
=FLOOR(A2,15/60/24)Again, change 15 to another interval as you need.
Round time to nearest 10 second
To precisely round time entries at the seconds level, such as 12:34:12 being rounded to 12:34:10 or 12:34:20, enter:
=MROUND(A2,10/86400)
In these formulas, you can easily adapt the rounding interval to other values, e.g., to round to the nearest 20 seconds use:
=MROUND(A2,20/86400)The number 86400 represents the total seconds in a day (24 × 60 × 60). Always remember to format the result as Time.
Error reminders and troubleshooting suggestions: If you find that your answers look like decimals or are incorrect, first check your cell references and then ensure the cell formatting is set to a Time type. If your numbers seem unexpected, verify your formula was not accidentally copied as text, and confirm that the interval specified matches your intended rounding granularity.
Quickly convert time to decimal value in Excel |
| For example, you have a list of time needed to be converted todecimal hours, minutes or seconds, how can you quickly and easily solve it in Excel? The Convert Time of Kutools for Excel, can do a favor for you. Click for 30 days free trial! |
 |
| Kutools for Excel: with more than 300 handy Excel add-ins, free to try with no limitation in 30 days. |
Relative Articles:
- Round date to nearest month in Excel
- Round date to previous/next specific weekday in Excel
- Round number to nearest5/10/50 in Excel
- Round number to nearest even or odd number in Excel
- Round and sum number in Excel
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license — set up in minutes (MSI-ready)
- Works better together — streamlined productivity across Office apps
- 30-day full-featured trial — no registration, no credit card
- Best value — save vs buying individual add-in