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.

Round / Round up / Round down time to nearest quarter / half hour with formulas

Round / Round up / Round down time to nearest quarter / half hour with formulas

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:

Notes:

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.