How to Round Time to the 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.

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. 

Data set with time

We apply the steps below:

  1. Select cell G2 and then enter the below formula in the cell
=MROUND(F2,"0:15")
Enter the formula in cell G2
  1. Press the Enter key to get the formula result in the cell.
press enter to confirm the formula
  1. 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) 
apply the Formula for the entire column

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

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. 

dataset with time values

We use the steps below:

  1. Select cell G2 and type in the following formula:
=CEILING.MATH(F2,1/96)
enter the ceiling formula in cell G2
  1. Press the Enter key to get the formula result in the cell.
confirm the formula by pressing enter
  1. 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):
copy the formula for the entire column
  1. Select and right-click the range G2:G6 and select Format Cells on the shortcut menu that appears:
right click on the result
  1. 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 a time format

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.

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. 

data set with time value

We use the following steps:

  1. Select cell G2 and then enter the below formula in the cell
=FLOOR.MATH(F2,1/96)
enter the Floor formula in cell G2
  1. Click the Enter button on the formula bar to enter the formula.
  1. 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):
copy the formula for the entire column
  1. 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
  1. 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. 

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. 

We use the following steps:

  1. Select cell G2 and type in the following formula:
=ROUND(F2*(24*60/15),0)/(24*60/15)
Enter the round formula in cell G2
  1. Press the Enter key to get the formula result in the cell
confirm the formula by pressing enter
  1. Drag down or double-click the fill handle to copy the formula down the column.
copy the formula for the entire column

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

Explanation of the formula

=ROUND(F2*(24*60/15),0)/(24*60/15)

The value in cell F2 is multiplied by the number of minutes in a day (24*60). The product is then divided by the time interval of 15 minutes.

This tutorial has presented four Excel functions that can be used to round time to the nearest quarter hour.

The functions are MROUND, CEILING.MATH, FLOOR.MATH, and ROUND. We hope that you found the tutorial helpful.

Other Excel articles you may also like: