How to Round Time to Nearest Minute in Excel (5 Suitable Ways)

Get FREE Advanced Excel Exercises with Solutions!

Sometimes you need to round time to the nearest minutes. You can easily do this in Excel with the easiest ways. This article will focus on how to round time to the nearest minutes in Excel with some useful examples. I think you will enjoy the whole article and gather some useful insides.


How to Round Time to Nearest Minute in Excel: 5 Easy Ways

Here, we have found five different and useful ways to round time to the nearest minutes. All the methods are fairly easy to digest and really easy to use.


1. Utilizing MROUND Function

Our first method is mainly based on the MROUND function. The MROUND function is basically round time to the nearest minute. We have a dataset that includes several times. We want to round the time to the nearest 15mins, 30mins, and 45mins respectively.


1.1. Nearest 15 Minutes

Firstly, we need to round the time to the nearest 15 minutes. To do this you need to follow the following steps.

Steps

  • Select cell C5.

Excel Round Time to Nearest Minute Using MROUND Function

  • Next, write down the following formula in the formula box.
=MROUND(B5,"0:15")

Excel Round Time to Nearest Minute Using MROUND Function

  • Press Enter to apply the formula.

  • Now, drag the Fill handle icon to cell C10 to apply the formula to that direction.

Excel Round Time to Nearest Minute Using MROUND Function

Read More: Rounding Time in Excel to Nearest Hour


1.2. Nearest 30 Minutes

If you need to round the time to the nearest 30 minutes, you can look at this process.

Steps

  • Select cell D5.

  • Next, write down the following formula in the formula box.
=MROUND(B5,"0:30")

Excel Round Time to Nearest Minute Using MROUND Function

  • Press Enter to apply the formula.

  • Now, drag the Fill handle icon to cell D10 to apply the formula to that direction.

Excel Round Time to Nearest Minute Using MROUND Function


1.3 Nearest 45 Minutes

You can round the time to the nearest 45 minutes. To do this you need to follow the following steps.

Steps

  • Select cell E5.

  • Next, write down the following formula in the formula box.
=MROUND(B5,"0:45")

  • Press Enter to apply the formula.

  • Now, drag the Fill handle icon to cell E10 to apply the formula to that direction.

Excel Round Time to Nearest Minute Using MROUND Function

Read More: Rounding Time to Nearest Quarter Hour in Excel


2. Using CEILING.MATH Function

Secondly, you can use the CEILING.MATH function to round the time in Excel up to the nearest direction. Here, we basically provide three valuable examples to round the time to the nearest direction.

Nearest 15 Minutes

Firstly, we need to round the time to the nearest 15 minutes. To do this you need to follow the following steps.

Steps

  • Select cell C5.

  • Next, write down the following formula in the formula box.
=CEILING.MATH(B5,"00:15")

Excel Round Time to Nearest Minute Using CEILING Function

  • Press Enter to apply the formula.

  • Now, drag the Fill handle icon to cell C10 to apply the formula to that direction.

Excel Round Time to Nearest Minute Using CEILING Function

Nearest 30 Minutes

If you need to round the time to the nearest 30 minutes, you can look at this process.

Steps

  • Select cell D5.

  • Next, write down the following formula in the formula box.
=CEILING.MATH(B5,"00:30")

Excel Round Time to Nearest Minute Using CEILING Function

  • Press Enter to apply the formula.

  • Now, drag the Fill handle icon to cell D10 to apply the formula to that direction.

Excel Round Time to Nearest Minute Using CEILING Function

Nearest 45 Minutes

You can round the time to the nearest 45 minutes. To do this you need to follow the following steps.

Steps

  • Select cell E5.

  • Next, write down the following formula in the formula box.
=CEILING.MATH(B5,"00:45")

Excel Round Time to Nearest Minute Using CEILING Function

  • Press Enter to apply the formula.

  • Now, drag the Fill handle icon to cell E10 to apply the formula to that direction.

Excel Round Time to Nearest Minute Using CEILING Function


3. Applying FLOOR Function

You can use the FLOOR function to round time to the nearest minute. The FLOOR Function actually provides the round time down to the nearest minutes.

Nearest 15 Minutes

Firstly, we can round the time down to the nearest 15 minutes. To do this, you need to follow the following steps.

Steps

  • Select cell C5.

  • Next, write down the following formula in the formula box.
=FLOOR(B5,"00:15")

Excel Round Time to Nearest Minute Using FLOOR Function

  • Press Enter to apply the formula.

Excel Round Time to Nearest Minute Using FLOOR Function

  • Now, drag the Fill handle icon to cell C10 to apply the formula to that direction.

Excel Round Time to Nearest Minute Using FLOOR Function

Nearest 30 Minutes

If you need to round the time to the nearest 30 minutes, you can look at this process.

Steps

  • Select cell D5.

  • Next, write down the following formula in the formula box.
=FLOOR(B5,"00:30")

  • Press Enter to apply the formula.

  • Now, drag the Fill handle icon to cell D10 to apply the formula to that direction.

Nearest 45 Minutes

You can round the time to the nearest 45 minutes. To do this you need to follow the following steps.

Steps

  • Select cell E5.

  • Next, write down the following formula in the formula box.
=FLOOR(B5,"00:45")

Excel Round Time to Nearest Minute Using FLOOR Function

  • Press Enter to apply the formula.

  • Now, drag the Fill handle icon to cell E10 to apply the formula to that direction.

Excel Round Time to Nearest Minute Using FLOOR Function

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


4. Utilizing ROUND Function

You can utilize the ROUND function to round time to the nearest minute. Here, we basically provide three valuable examples to round the time to the nearest direction.

Nearest 15 Minutes

Firstly, we need to round the time to the nearest 15 minutes. To do this you need to follow the following steps.

Steps

  • Select cell C5.

Excel Round Time to Nearest Minute Using MROUND Function

  • Next, write down the following formula in the formula box.
=ROUND(B5*(24*60/15),0)/(24*60/15)

Excel Round Time to Nearest Minute Using ROUND Function

  • Press Enter to apply the formula.

  • Now, drag the Fill handle icon to cell C10 to apply the formula to that direction.

Excel Round Time to Nearest Minute Using ROUND Function

Nearest 30 Minutes

If you need to round the time to the nearest 30 minutes, you can look at this process.

Steps

  • Select cell D5.

  • Next, write down the following formula in the formula box.
=ROUND(B5*(24*60/30),0)/(24*60/30)

Excel Round Time to Nearest Minute Using ROUND Function

  • Press Enter to apply the formula.

  • Now, drag the Fill handle icon to cell D10 to apply the formula to that direction.

Excel Round Time to Nearest Minute Using ROUND Function

Nearest 45 Minutes

You can round the time to the nearest 45 minutes. To do this you need to follow the following steps.

Steps

  • Select cell E5.

  • Next, write down the following formula in the formula box.
=ROUND(B5*(24*60/45),0)/(24*60/45)

  • Press Enter to apply the formula.

  • Now, drag the Fill handle icon to cell E10 to apply the formula to that direction.


5. Using Combined Formula

Next, we can round time to the nearest minute by utilizing the combination of TIME, HOUR, MROUND, MINUTE, and SECOND functions in Excel. We show three different examples of this method to understand it clearly.

Nearest 45 Minutes

Firstly, we need to round the time to the nearest 15 minutes. To do this you need to follow the following steps.

Steps

  • Select cell C5.

  • Next, write down the following formula in the formula box.
=TIME(HOUR(B5),MROUND(MINUTE(B5)+SECOND(B5)/60,15),0)

Excel Round Time to Nearest Minute Using COMBINATION Function

  • Press Enter to apply the formula.

  • Now, drag the Fill handle icon to cell C10 to apply the formula to that direction.

Excel Round Time to Nearest Minute Using COMBINATION Function

Nearest 30 Minutes

If you need to round the time to the nearest 30 minutes, you can look at this process.

Steps

  • Select cell D5.

  • Next, write down the following formula in the formula box.
=TIME(HOUR(B5),MROUND(MINUTE(B5)+SECOND(B5)/60,30),0)

Excel Round Time to Nearest Minute Using COMBINATION Function

  • Press Enter to apply the formula.

  • Now, drag the Fill handle icon to cell D10 to apply the formula to that direction.

Excel Round Time to Nearest Minute Using COMBINATION Function

Nearest 45 Minutes

You can round the time to the nearest 45 minutes. To do this you need to follow the following steps.

Steps

  • Select cell E5.

  • Next, write down the following formula in the formula box.
=TIME(HOUR(B5),MROUND(MINUTE(B5)+SECOND(B5)/60,45),0)

Excel Round Time to Nearest Minute Using COMBINATION Function

  • Press Enter to apply the formula.

  • Now, drag the Fill handle icon to cell E10 to apply the formula to that direction.

Excel Round Time to Nearest Minute

Breakdown of the Formula

  • First, to provide to time of cell B5 including hours, minutes, and seconds, we use the TIME function.
  • As we want to round time to the nearest minute, we set MINUTE and SECOND as zero.
  • In the MROUND function, we set the multiplier as 15,30, or 45 minutes to round the nearest time of cell B5. It will provide us with the desired solution.

Download Practice Workbook

Download the practice workbook


Conclusion

We have shown five different methods to round time to the nearest minute in Excel. We have also discussed each method with three different examples. I think it will give you a clear view of how to round time to the nearest minute. If you have any further questions, feel free to ask in the comment box. Don’t forget to visit our Exceldemy page.


Related Articles

What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Durjoy Paul
Durjoy Paul

Hi there! I'm Durjoy. I have completed my graduation from the Bangladesh University of Engineering and Technology. I am working and doing research on Microsoft Excel and here I will be posting articles related to it.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo