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
Round time to nearest 1 hour
Select a cell and type this formula =MROUND(A2,"1:00") into it, and press Enter key, then drag autofill handle to the cells you need.
Tips:
1. You need to format the cells as time format firstly by selecting cells and right clicking to select Format Cells in the context menu; in the Format Cells dialog, selecting Time in Category list and then selecting a time type in Type list, and clicking OK.
2. You also can use these formulas to round time to nearest hour =ROUND(A2/(1/24),0)*(1/24) or =MROUND(A2,1/24).
3. If you want to round time to previous 1 hour, you can use this formula =FLOOR(A2,1/24).
4. In above formulas, you can change 1 to other number as you need.
Round time to nearest 15 minute
Select a cell and type this formula =ROUND(A2*(24*60/15),0)/(24*60/15) into it, and press Enter key, then drag autofill handle to the cells you need.
Tip:
1. You need to format the cells as time format firstly by selecting cells and right clicking to select Format Cells in the context menu; in the Format Cells dialog selecting Time in Category list, selecting a time type in Type list, and clicking OK.
2. You also can use this formula =MROUND(A2,15/60/24) to round time to nearest minute.
3. If you want to round time to previous 15 minute, use this formula =FLOOR(A2,15/60/24).
4. In above formulas, you can change 15 to other number as you need.
Round time to nearest 10 second
Select a cell and type this formula =MROUND(A2, 10/86400) into it, and press Enter key, then drag autofill handle to the cells you need.
Tip:
1. You need to format the cells as time format firstly by selecting cells and right clicking to select Format Cells in the context menu, then go to select a time type in the Format Cells dialog.
2. In above formula, you can change 10 to other number as you need. For example, use =MROUND(A2, 20/86400) to round time to nearest 20 second.
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 nearest 5/10/50 in Excel
- Round number to nearest even or odd number in Excel
- Round and sum number in Excel
Best Office Productivity Tools
Supports Office/Excel 2007-2021 and 365 | Available in 44 Languages | Easy to Uninstall Completely
Kutools for Excel Boasts Over 300 Features, Ensuring That What You Need Is Just A Click Away...
Supercharge Your Excel Skills: Experience Efficiency Like Never Before with Kutools for Excel (Full-Featured 30-Day Free Trial)
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! (Full-Featured 30-Day Free Trial)
