Round Time to Nearest Quarter Hour in Excel

Time tracking is useful when trying to improve efficiency in your projects (or even your personal life).

If you work with time datasets in Excel, it would be useful to know how to round time to the nearest quarter of an hour (especially when planning meetings or creating agendas)

This tutorial gives four examples of how to round time values to the nearest quarter hour in Excel.

Table of Contents

Method #1: Round Time to the Nearest Quarter Hour Using the MROUND Function

The MROUND function returns a number rounded to the specified multiple.

The function takes two required arguments, the value you want to round and the multiple to which you want to round the value.

Suppose we are working on the following billing Excel worksheet and want to round the elapsed time in column F to the nearest quarter hour or 15 minutes.

We apply the steps below:

Select cell G2 and then enter the below formula in the cell

=MROUND(F2,"0:15")

Press the Enter key to get the formula result in the cell.

Copy the formula to all the other cells in the column (you can copy and paste using Control C and Control V, or double-click on the Fill Handle)

The elapsed time has been rounded to the nearest quarter hour.

Explanation of the formula

=MROUND(F2,"0:15")

The syntax of the MROUND function is MROUND(number, multiple).

According to this syntax, the value in cell F2 is the number, and 0:15 is multiple.

Furthermore, the multiple specifies that the formula must return the nearest quarter hour, that is, 0:45.

Note: MROUND only rounds up (away from zero) if the minutes remaining after dividing the number by multiple are equal to or greater than half the value of the multiple. Otherwise, it rounds down (towards zero) to the nearest quarter hour. So 0:49 minutes would become 0:45 and 0:54 minutes would become 1:00

Also read: How to Add Hours to Time in Excel?

Method #2: Round Time to the Nearest Quarter Hour Using the CEILING.MATH Function

The CEILING.MATH function allows us to round a number up to the nearest multiple of significance or the nearest integer.

The function takes one required argument and two optional arguments. The number argument is required, which is the number to be rounded.

The syntax of the CEILING.MATH function is

CEILING.MATH(number, [significance], [mode]).

The significance argument is optional, and it is the multiple to which the number argument is to be rounded.

The mode argument is also optional and, if mentioned, would determine whether the number argument is to be rounded up away from zero or rounded down toward zero.

Suppose we are working on the following billing Excel worksheet and want to round up the elapsed time in column F to the nearest quarter hour or 15 minutes.

We use the steps below:

Select cell G2 and type in the following formula:

=CEILING.MATH(F2,1/96)

Press the Enter key to get the formula result in the cell.

Copy the formula to all the other cells in the column (you can copy and paste using Control C and Control V, or double-click on the Fill Handle):

Select and right-click the range G2:G6 and select Format Cells on the shortcut menu that appears:

In the Format Cells dialog box that appears, select Time in the Category list box and 13:30 in the Type list box, and click OK.

The values in column G are displayed in the Time format. The elapsed time has been rounded up to the nearest quarter hour.

the resulting values are shown in the right time format

Explanation of the formula

=CEILING.MATH(F2,1/96)

The syntax of the CEILING.MATH function is CEILING.MATH(number, [significance], [mode]).

According to this syntax, the value in cell F2 is the number argument, 1/96 is the multiple to which the number argument is to be rounded, and the mode argument has been omitted because we are not dealing with negative numbers in this case.

In the case of the time of 1:05, for example, the multiple of 1/96 specifies that it must be rounded up to 1:15.

Note: One day has 24 hours and 96 quarter hours (24*4). Therefore one-quarter hour or 15 minutes is equal to 1/96. We have used the fraction 1/96 in our formula because it is easier to remember than the decimal 0.0104166666666667.

Also read: How to Round Numbers in Excel Without Formula?

Method #3: Round Time to the Nearest Quarter Hour Using the FLOOR.MATH Function

The FLOOR.MATH function rounds a number to the nearest multiple of significance or the nearest integer.

The function takes one required argument and two optional arguments. The number argument is required, which is the number to be rounded down.

The syntax of the FLOOR.MATH function is

FLOOR.MATH(number, [significance], [mode]).

The significance argument is optional, and it is the multiple to which the number argument is to be rounded.

The mode argument is also optional and determines whether negative number arguments are to be rounded up away from zero or rounded down toward zero.

Suppose we are working on the following billing Excel worksheet and want to round down the elapsed time in column F to the nearest quarter hour or 15 minutes.

We use the following steps:

Select cell G2 and then enter the below formula in the cell

=FLOOR.MATH(F2,1/96)

Click the Enter button on the formula bar to enter the formula.

Copy the formula to all the other cells in the column (you can copy and paste using Control C and Control V, or double-click on the Fill Handle):

Select range G2:G6, right-click it, and select Format Cells on the shortcut menu that appears:

right click on the cells and click on format

In the Format Cells dialog box that appears, select Time in the Category list box and 13:30 in the Type list box, and click OK.

select time format in which you want to show the result

The values in column G are displayed in the Time format. The elapsed time has been rounded down to the nearest quarter hour.

resulting values where time has been rounded to the nearest quarter

Explanation of the formula

=FLOOR.MATH(F2,1/96)

The syntax of the FLOOR.MATH function is FLOOR.MATH(number, [significance], [mode]).

According to this syntax, the value in cell F2 is the number to be rounded down.

The fraction 1/96 is the multiple to which the number argument is to be rounded, and the mode argument has been omitted because, in this case, we are not dealing with negative numbers.

In the case of the time of 1:05, for example, the multiple of 1/96 specifies that it must be rounded down to 1:00.

Also read: Round to the Nearest Tenth in Excel

Method #4: Round Time to the Nearest Quarter Hour Using the ROUND function

The ROUND function rounds a number to a specified number of digits.

The function takes two required arguments, the number argument, which is the number to be rounded, and the num-digits argument, which is the number of digits to which the number argument is to be rounded.

Imagine we are working on the following billing Excel worksheet and want to round down the elapsed time in column F to the nearest quarter hour or 15 minutes.