How to round / round up / round down time to nearest quarter / half hour in Excel?
This article, I will talk about how to round / round up / round down a column of time format to nearest quarter or half hour in a worksheet as following screenshot shown.
If you want to round / round up / round down the time to nearest quarter or half hour, the following formulas can help you.
1. Enter this formula: =MROUND(A2,"0:15") or =MROUND(A2,"0:30") into a blank cell beside your data which need to round to nearest quarter or half hour, and then drag the fill handle down to the cells you want to apply this formula, and you will get a list of decimal numbers, see screenshot:
2. Then format the numbers to time format, select the number list, and then right click, choose Format Cells from the context menu, see screenshot:
3. In the popped out Format Cells dialog box, click Custom from the Category pane, and select [h]:mm:ss format in the Type list box, see screenshot:
4. Then click OK, and all the cells have been rounded to a nearest quarter hour, see screenshot:
1. To round up a time to nearest quarter or half hour, please apply this formula: =CEILING(A2,"00:15") or =CEILING(A2,"00:30"), and remember to format the cells as time format.
2. To round down time to nearest quarter or half hour, please apply this formula: =FLOOR(A2, "00:15") or =FLOOR(A2, "00:30"), and remember to format the cells as time format.
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)