How to Round Off Time in Excel – Using MROUND Function

Dealing with time in excel becomes a challenge especially when you do not know the right approach to the same. In this blog, we are going to learn how to Round off the given time values to the nearest hour, minute, or second.

So let us begin learning.

What is MROUND Function?

The MROUND function rounds off the given value to the multiple defined by the user. The function always rounds off the number in the direction away from zero.

You can use the following practice workbook to move along with us.

=MROUND(number,multiple)

The MROUND Function requires two function arguments.

• number – This is going to be the time that we want to round off.
• multiple – This is the actual multiple around which we want to round off the time value.

How to Round Off Time to Nearest Hour Value

Let us suppose we have the following time values.

Note that these values are in the time format hh:mm. The resultant rounded-off to hours would also be in time format. To change the format to time:-

• Select the range A2:A6.
• Press Ctrl 1 key. Navigate to the Number Tab of the Format Cells Dialog box.
• Choose Custom Number Format and choose hh:mm in the type field. Click Ok.

Now you can use the following MROUND function formula to round off the following time values to the nearest hour value. Use this formula in cell B2 and copy it down.

=MROUND(A2,"1:00")

As a result, you can see that the function has rounded off the time to the nearest hour.

Explanation – We have supplied the number argument of the MROUND Function as the time contained in column A. This is so because we wanted to round off the time. The multiple argument is supplied in double quotes and it is 1:00 as we wanted to round it off as the nearest hour multiple.

Round Off Time to Multiple of 15 Minutes

In this example, we would round off time to a multiple of 15 minutes. The rounded-off time would be in form hh:15, hh:30, hh:45, or hh:00.

Now you can use the following MROUND function formula to get the time as a multiple of 15 minutes.

=MROUND(A2,"00:15")

We used the formula in cell B2 and then copied the formula down for the rest of the cells. As a result, the MROUND Function rounds off the time.

Explanation – We have supplied the multiple argument of the MROUND Function as 0:15 in double quotes. Here the minute part is 15 so that the time is rounded off as a multiple of 15 minutes.

Rounding Off the given Time as a Multiple of 30 Seconds

We can round off the time to a multiple of 30 seconds. This resultant rounded off time would be either in the form hh:mm:00 or hh:mm:30.

You can simply use the following formula to round off the time.

=MROUND(A2,"00:00:30")

This brings us to the end of the blog.

Thank you for reading.