How to Round Time to 30 Minutes or 15 Minutes in Excel

This updated guide examines How to Round Time to 30 Minutes or 15 Minutes in Excel and organizes the essential facts, background, and practical takeaways in clear American English.

1. How to use the MOD function to round time to about half an hour

At the time table to be rounded, in the first time input box enter the formula = MOD (INT (B2 / (1/48)) * (1/48), 1) where B2 is the value cell time wants to be rounded.

Output the decimal number as shown below. Next you need to change the format for this cell to Time is okay. You click on the results box and then in the Number group on the toolbar select the format as Time as shown.

Now the cell has been converted to the time format and rounded to half an hour ago as shown. Now we just need to drag the first cell result down to the remaining cells.

2. How to use the FLOOR function rounds time to about 15 minutes

To round the time back to 15 minutes ago, we will use the FLOOR function. In the first cell enter the result of rounding time to 15 minutes, enter the formula = FLOOR (B2; TIME (0; 15; 0)) . Note that if you want to round to another value, then enter that time in the formula is okay.

If the result shows a decimal, you also need to change the format to Time.

If you want, you can also round the time back to 20 minutes ago with the same formula.

And you also get the result of the time rounding.