Rounding Time to Nearest Quarter Hour in Excel (6 Easy Methods)

Get FREE Advanced Excel Exercises with Solutions!

If you are looking for some special tricks of rounding time in Excel to the nearest quarter-hour, you’ve come to the right place. In Microsoft Excel, there are numerous ways of rounding time in Excel to the nearest quarter-hour. This article will discuss six methods of rounding time in Excel to the nearest quarter-hour. Let’s follow the complete guide to learn all of this.


Rounding Time to Nearest Quarter Hour in Excel: 6 Easy Ways

In the following section, we will use six effective and tricky methods of rounding time in Excel to the nearest quarter-hour. This section provides extensive details on six methods. You should learn and apply all of these to improve your thinking capability and Excel knowledge.


1. Using MROUND Function

Here, we will demonstrate how to round time in Excel to the nearest quarter-hour. Let us first introduce you to our Excel dataset so that you are able to understand what we are trying to accomplish with this article. Here, in the dataset,  the details of the exit times for every employee of a company are provided. Now, we want to round time to the nearest quarter-hour by using the MROUND function. Let’s walk through the steps to round time in Excel to the nearest quarter-hour.

Using MROUND Function

📌 Steps:

  • Firstly, we will use the following formula in the cell D5:

=MROUND(C5,"0:15")

Here, the MROUND function is used to round the time of cell C5 to the nearest quarter-hour. We set the multiplier to “0:15” since quarters equal 0.15.

  • Press Enter.

Rounding Time to Nearest Quarter Hour in Excel

  • Next, drag the Fill handle icon.
  • Therefore, you can round time in Excel to the nearest quarter-hour, as shown below.

Using MROUND Function

Read More: Round off Formula in Excel Invoice


2. Applying FLOOR Function

Here, we will show another effective method to round time in Excel to the nearest quarter-hour by using the FLOOR function. Let’s walk through the steps to round time in Excel to the nearest quarter-hour.

📌 Steps:

  • Firstly, we will use the following formula in the cell D5:

=FLOOR(C5,"00:15")

Here, the FLOOR function is used to round the time of cell C5 to the nearest quarter-hour. We set the multiplier to “0:15” since quarters equal 0.15.

  • Press Enter.

Applying FLOOR Function

  • Next, drag the Fill handle icon.
  • Therefore, you can round time in Excel to the nearest quarter-hour, as shown below.

Rounding Time to Nearest Quarter Hour in Excel

Read More: How to Remove Decimals in Excel with Rounding


3. Implementing CEILING.MATH Function

Now, we will demonstrate another quickest method to round time in Excel to the nearest quarter-hour by using the CEILING.MATH function. Let’s walk through the steps to round time in Excel to the nearest quarter-hour.

📌 Steps:

  • Firstly, we will use the following formula in the cell D5

=CEILING.MATH(C5,"00:15")

Here, the CEILING.MATH function is used to round the time of cell C5 to the nearest quarter-hour. We set the multiplier to “0:15” since quarters equal 0.15.

  • Press Enter.

Implementing CEILING.MATH Function

  • Next, drag the Fill handle icon.
  • Therefore, you can round time in Excel to the nearest quarter-hour, as shown below.

Rounding Time to Nearest Quarter Hour in Excel

Read More: How to Round to Nearest 10 Cents in Excel


Similar Readings


4. Utilizing ROUND Function

By using the ROUND function in Excel, we can also quickly round time to the nearest quarter-hour. Let’s walk through the steps to round time in Excel to the nearest quarter-hour.

📌 Steps:

  • Firstly, we will use the following formula in the cell D5:

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

Here, the ROUND function is used to round the time of cell C5 to the nearest quarter-hour. We multiply 24*60/15 with the cell value in Excel, since one hour is equal to 1/24. Num digits are set to 0, as we do not want decimal values. After the whole formula is divided by 24*60/15, it is converted to the nearest quarter-hour.

  • Press Enter.

Utilizing ROUND Function

  • Next, drag the Fill handle icon.
  • Therefore, you can round time in Excel to the nearest quarter-hour, as shown below.

Rounding Time to Nearest Quarter Hour in Excel

Read More: How to Add ROUND Formula to Multiple Cells in Excel


5. Using Combined Formula

By combining TIME, HOUR, MINUTE, SECOND, and MROUND functions in Excel, we can also quickly round time to the nearest quarter-hour. Let’s walk through the steps to round time in Excel to the nearest quarter-hour.

📌 Steps:

  • Firstly, we will use the following formula in the cell D5:

=TIME(HOUR(C5),MROUND(MINUTE(C5)+SECOND(C5)/60,15),0)

  • Then, press Enter.

Using Combined Formula

  • Next, drag the Fill handle icon.
  • Therefore, you can round time in Excel to the nearest quarter-hour, as shown below.

Rounding Time to Nearest Quarter Hour in Excel

🔎 How Does the Formula Work?

  • With the help of the TIME function, we present the time of cell C5 with hours, minutes, and seconds.
  • We set minute and second to 0 since want the nearest quarter-hour.
  • Then, we use the MROUND function, multiply the formula by 60, and set the multiplier as 15 to round the time of cell C5 to the nearest quarter-hour.
  • Finally, we get the output time of 3:15 PM in cell D5.

Read More: Round to Nearest 5 or 9 in Excel


6. Implementing Nested IF Formula in Excel

By using the IF function in Excel, we can also quickly round time to the nearest quarter-hour. Here, we also use HOUR and MINUTE functions. Let’s walk through the steps to round time in Excel to the nearest quarter-hour.

Implementing Nested IF Formula in Excel

📌 Steps:

  • Firstly, select the range of cell D5:F13 and select General to maintain a proper format of the cell.

Implementing Nested IF Formula in Excel

  • Next, we will use the following formula in the cell D5:

=HOUR(C5)

Here, the HOUR function returns the hour as a number from cell C5.

  • Then, press Enter.

Rounding Time to Nearest Quarter Hour in Excel

  • Next, drag the Fill handle icon.
  • Therefore, you can get the Hour column, as shown below.

Implementing Nested IF Formula in Excel

  • Next, we will use the following formula in the cell E5:

=MINUTE(C5)

Here, the MINUTE function returns the minute as a number from cell C5.

  • Then, press Enter.

Rounding Time to Nearest Quarter Hour in Excel

  • Next, drag the Fill handle icon.
  • Therefore, you can get the Minutes column, as shown below.

Rounding Time to Nearest Quarter Hour in Excel

  • Next, we will use the following formula in the cell F5:

=IF(E5<8,0,IF(E5<23,15,IF(E5<38,30,IF(E5<53,45,60))))

Here, the IF function tests whether the conditions are met or not, and returns a value accordingly.

  • Then, press Enter.

Rounding Time to Nearest Quarter Hour in Excel

  • Next, drag the Fill handle icon.
  • Therefore, you can round time in Excel to the nearest quarter-hour, as shown below.

Implementing Nested IF Formula in Excel

Read More: How to Round to Nearest Whole Number in Excel


Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.


Conclusion

That’s the end of today’s session. I strongly believe that from now you may round time in Excel to the nearest quarter-hour. If you have any queries or recommendations, please share them in the comments section below.

Don’t forget to check our website Exceldemy.com for various Excel-related problems and solutions. Keep learning new methods and keep growing!


Related Articles

Saquib Ahmad Shuvo
Saquib Ahmad Shuvo

Welcome to my Profile. I am working on and researching Microsoft Excel right now, and I will be posting articles about it here. I received a B.Sc. in Naval Architecture and Marine Engineering from the Bangladesh University of Engineering and Technology (BUET). Having studied naval architecture, I have a strong interest in research and development. Always try to learn from different sources and come up with creative solutions.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo