You can use the following formula to round time values to the nearest quarter hour in Excel:
=MROUND(A2, "0:15")
This particular formula will round the time value in cell A2 to the nearest quarter hour.
For example, if cell A2 contains 2:25:01 AM then this formula will round this time to 2:30:00 AM since this represents the nearest quarter hour.
The following example shows how to use this formula in practice.
Example: Round to Nearest Quarter Hour in Excel
Suppose we have the following column of times in Excel:
Suppose we would like to round each time to the nearest quarter hour.
To do so, we can type the following formula into cell B2:
=MROUND(A2, "0:15")
We can then click and drag this formula down to each remaining cell in column B:
Column B now displays each time in column A rounded to the nearest quarter hour.
For example:
- 1:15:32 AM is rounded to 1:15:00 AM
- 2:25:01 AM is rounded to 2:30:00 AM
- 8:39:45 AM is rounded to 8:45:00 AM
And so on.
Note that if your time values don’t include AM and PM values, then this formula will produce a decimal instead of a time.
To format the decimal as a time, you need to highlight the cells in column B, right click, and click Format Cells… from the dropdown menu.
In the window that appears, click Custom from the Category list, then type [h]:mm:ss in the Type box, then click OK.
The decimals will be converted to time values.
Note: You can find the complete documentation for the MROUND function in Excel here.
Additional Resources
The following tutorials explain how to perform other common operations in Excel:
Excel: Calculate Difference Between Two Times in Hours
Excel: Calculate Difference Between Two Times in Minutes
Excel: How to Calculate Average Time
