Durjoy Paul
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.
Download Practice Workbook
Download the practice workbook
5 Easy Ways to Round Time to Nearest Minute in Excel
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.
- Next, write down the following formula in the formula box.
=MROUND(B5,"0:15")
- Press Enter to apply the formula.
- Now, drag the Fill handle icon to cell C10 to apply the formula to that direction.
Read More: Rounding Time in Excel to Nearest Hour (6 Easy Methods)
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")
- Press Enter to apply the formula.
- Now, drag the Fill handle icon to cell D10 to apply the formula to that direction.
Read More: How to Add ROUND Formula to Multiple Cells in Excel (2 Easy Ways)
Similar Readings
- How to Round Percentages in Excel (4 Simple Methods)
- Excel VBA: Round to Nearest 5 (Macro and UDF)
- How to Remove Decimals in Excel with Rounding (10 Easy Methods)
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.
Read More: Rounding Time to Nearest Quarter Hour in Excel (6 Easy Methods)
2. Using CEILING.MATH Function
Secondly, you can use the CEILING.MATH function to round the time 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")
- Press Enter to apply the formula.
- Now, drag the Fill handle icon to cell C10 to apply the formula to that direction.
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")
- 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.
=CEILING.MATH(B5,"00:45")
- Press Enter to apply the formula.
- Now, drag the Fill handle icon to cell E10 to apply the formula to that direction.
Read More: Round to Nearest Whole Number in Excel (9 Easy Methods)
Similar Readings
- How to Round to Nearest 10 Cents in Excel (4 Suitable Methods)
- Round off Formula in Excel Invoice (9 Quick Methods)
- How to Round a Formula with SUM in Excel (4 Simple Ways)
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")
- Press Enter to apply the formula.
- Now, drag the Fill handle icon to cell C10 to apply the formula to that direction.
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")
- Press Enter to apply the formula.
- Now, drag the Fill handle icon to cell E10 to apply the formula to that direction.
Read More: How to Round Down to Nearest Whole Number in Excel (4 Methods)
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.
- Next, write down the following formula in the formula box.
=ROUND(B5*(24*60/15),0)/(24*60/15)
- Press Enter to apply the formula.
- Now, drag the Fill handle icon to cell C10 to apply the formula to that direction.
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)
- 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.
=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.
Read More: Round to Nearest 5 or 9 in Excel (8 Easy Methods)
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)
- Press Enter to apply the formula.
- Now, drag the Fill handle icon to cell C10 to apply the formula to that direction.
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)
- 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.
=TIME(HOUR(B5),MROUND(MINUTE(B5)+SECOND(B5)/60,45),0)
- Press Enter to apply the formula.
- Now, drag the Fill handle icon to cell E10 to apply the formula to that direction.
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.
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.
