3 Formulas to Round Time to the Nearest Minute in Excel
Microsoft Excel has multiple functions to round off times. Among them, the ROUND, MROUND, FLOOR, CEILING, etc. are some of the functions. In this tutorial, I will show you how to round off time to the nearest minute in Excel using the ROUND and MROUND functions.
Introduction to the Dataset
In the column, Timestamp, I have some random timestamps. In the column, Nearest Minute, I will use formulas to round all the timestamps to their nearest minutes.
Easiest Way to Round Off Time to the Nearest Minute
Excel has a dedicated function to round off times to a certain multiple. The name of the function is MROUND. Now I will use the MROUND function to round off the timestamps to their minutes in Excel.
Syntax
=MROUND(number, multiple)Formula
=MROUND(A2,"0:01")Usage Guide
Step_1: Type the formula in cell B2.
Step_2: Press ENTER.
Step_3: Copy down the formula to cell B10 by dragging the lower-right corner of cell B2.
Final Result
The MROUND function rounds off all the timestamps to their corresponding nearest minutes.
For example, the timestamp 2:25:23 PM has two bracket timestamps that are the nearest whole minutes. They are 2:25:00 PM and 2:26:00 PM. The timestamp 2:25:00 PM is closer to 2:25:23 PM than that of 2.26:00 PM.
Thus, the formula has returned 2:25:00 PM in cell B2 instead of 2:26:00 PM.
Formula Explanation
According to the syntax of the MROUND function, cell A2 is the number. And “0:01” is multiple. The multiple specifies that the formula must return the nearest minute of the specified minute i.e. 2:25:00 PM.
If you mention any other multiple other than 0:01, the formula will return the corresponding nearest multiple of the specified minute.
- [3 Ways] Round Off Time to Nearest Multiple of 5 Minutes in Excel
- 2 Ways to Round to Nearest 50 Cents in Excel
Alternative Way #1: Using the ROUND Function
You can also use the ROUND function instead of the MROUND function. But you need to add some extra weights in order to calculate the nearest minutes. Now let me show you how to round off time to the nearest minutes.
Syntax
=ROUND(number, num_digits)Formula
=ROUND(A2*1440,0)/1440Usage Guide
Step_1: Type the formula in cell B2.
Step_2: Press ENTER.
Step_3: Copy down the formula to cell B10 by dragging the lower-right corner of cell B2.
Final Result
The above formula rounds off all the timestamps to their corresponding nearest minutes.
For example, the timestamp 2:25:23 PM has two bracket timestamps that are the nearest whole minutes. They are 2:25:00 PM and 2:26:00 PM. The timestamp 2:25:00 PM is closer to 2:25:23 PM than that of 2.26:00 PM.
Thus, the formula has returned 2:25:00 PM in cell B2 instead of 2:26:00 PM.
Formula Explanation
The formula used above is ROUND(A2*1440,0)/1440. We can rewrite the formula as ROUND(A2*24*60,0)/24*60.
First, the time is converted to minutes. This is done by multiplying the time with 1440. Then the fraction is rounded off using the ROUND function. In the formula, 0 specifies that there will be no decimal places after the decimal point. Thus the ROUND function returns solid rounded minutes of the time in cell A2.
Finally, the rounded minutes are divided by 1440 to get back to the original time format again.
- 4 Formulas to Round Off Prices To Nearest 10 Cents in Excel
- 5 Ways to Round Up a Formula Result in Excel
Alternative Way #2: Using TIME, HOUR, MINUTE, SECOND, & ROUND Functions
Finally, I will combine the TIME, HOUR, MINUTE, SECOND, & ROUND functions. This combination will return the rounded-off times to their nearest minutes.
Syntax
=TIME(HOUR(time),MINUTE(time),ROUND((SECOND(time)/60)*1,0)*60)Formula
=TIME(HOUR(A2),MINUTE(A2),ROUND((SECOND(A2)/60)*1,0)*60)Usage Guide
Step_1: Type the formula in cell B2.
Step_2: Press ENTER.
Step_3: Copy down the formula to cell B10 by dragging the Fill Handle.
Final Result
The formula converts all the timestamps to their nearest minutes. To return the nearest minutes, sometimes the formula rounded up the timestamps. Sometimes the formula is also rounded down the timestamps to calculate the nearest minutes.
Formula Explanation
Here, the main formula is made up of the TIME function.
The syntax of the TIME function is:
=TIME(hour, minute, second)The main idea behind the formula is to work with hours, minutes, and seconds separately.
- HOUR(A2) returns 14. But it is converted to 2 as the cell format is set to 12-Hour time format.
- MINUTE(A2) returns the minute part of the time in cell A2.
- SECOND(A2)/60 returns the second part of the time in cell A2.
- ROUND((SECOND(A2)/60)*1,0) rounds off the seconds returned by SECOND(A2)/60 to the nearest whole minute.
- Finally, the TIME function returns the converted time of cell A2 into the hour, minute, and second format.
Conclusion
So, I have discussed 3 formulas to round off time to the nearest minutes in Excel. I expect you’ve found this article helpful. You can read more Excel articles from our website’s Blog page. Have a great workday!
Related Articles
- How to Round Off Numbers in Excel (9 Methods)
- 3 Formulas for Rounding Time to the Nearest Hour in Excel
- [3 Formulas] Round Off Time to Nearest Multiple of 15 Minutes in Excel
- 5 Ways to Round a Formula with SUM Function in Excel
- 2 Ways to Stop Excel from Rounding up Percentages
- How to Stop Excel from Rounding 16-Digit Numbers (2 Methods)
![65 Shortcut Keys in Excel [Free PDF Download]](https://excelgraduate.com/wp-content/uploads/2022/02/65-Shortcut-Keys-in-Excel.png)
![[3 Ways] Round Off Time to Nearest Multiple of 5 Minutes in Excel](https://excelgraduate.com/wp-content/uploads/2022/07/excel-round-time-to-nearest-5-minutes-2.png)
everyone seems to think that round to nearest is useful, rather than next. phone companies for instance charge per minute, so 3s becomes 1m, but I can not find a formula to round up to the NEXT whole minute, not the nearest minute.
Hi Kris,
The following formula will convert any timestamp to the next whole minute but not the nearest minute.
=ROUNDUP(A2*1440,0)/1440
For example, 2:25:23 PM will become 2:26:00 PM.
Hope this helps. Thanks!