# Round Time to the Nearest 5 Minutes in Excel – 4 Methods

The dataset contains a column with time. To round the time to the nearest 5 minutes:

### Method 1 – Using the ROUND Function

STEPS:

• Set the cells of the 2 columns: Time and Rounded in Time format.
• Select the cells and go to Home > Number, and click the arrow icon.

• In Format Cells, go to Number > Time and select a time format.
• Click OK.

• Enter the formula in D5.
`=ROUND(C5*(24*60/5),0)/(24*60/5)`

Formula Breakdown

Step Value After the Step Explanation
Time 5:43 AM
Time in Number Format 0.24 This is the value of time in number format. The formula is: =(5*60+43)/(24*60)
(24*60/5) 288.00 Turns the full day of 24 Hours into 288 parts, 5 minutes each.
C5*(24*60/5) 68.64 It calculates how many 5 minutes are present in 5:43 PM
ROUND(C5*(24*60/5),0) 69.00 It rounds the value 68.64 to its nearest integer 69.
ROUND(C5*(24*60/5),0)/(24*60/5) 0.239583333 Dividing 69 by 288 returns time in number format.
In Time Format 5:45:00 AM Formats it to time again to return the nearest time rounded to 5 minutes
• Drag down the Fill Handle to see the result in the rest of the cells.

• Time values are rounded to the nearest 5 minutes.

### Method 2 – Using the MROUND Function

STEPS:

• Enter the formula in D5.
`=MROUND(C5,"0:05")`

Formula Breakdown

Step Value After the Step Explanation
Time 5:43 AM
C5   The cell that contains the time value
0:05 Sets “00:05” in multiple values.
MROUND(C5,”0:05″) 5:45 AM Rounds the minute value to the nearest multiple of 5
• Drag down the Fill Handle to see the result in the rest of the cells.

• This is the output.

### Method 3 – Round Time to Its Next 5-Minute Using the CEILING Function

• Enter this formula in D5.
`=CEILING(C5,0.5/144)`

Formula Breakdown

Step Value After the Step Explanation
Time 5:43 AM The value of time which will be rounded
Time in Number Format 0.238322 The value of time in number format. The formula is: =(5*60+43)/(24*60)
C5 The cell that contains the time value
0.5/144 0.003472 The amount of significance. The ceiling function will round the target to a next higher value.
CEILING(C5,0.5/144) 0.239583 The rounded value in number format.
Convert to Time Format 5:45 AM The value converted to time format:  the rounded time to the next nearest 5 minutes.
• Drag down the Fill Handle to see the result in the rest of the cells.

### Method 4 – Round Time to Its Last 5-Minutes Using the FLOOR Function

• Enter the formula in C5.

`=FLOOR(C5,0.5/144)`

Formula Breakdown

Step Value After the Step Explanation
Time 5:43 AM
Time in Number Format 0.238322 The value of time in number format. The formula is: =(5*60+43)/(24*60)
C5 The cell that contains the time value
0.5/144 0.003472 The amount of significance. The floor function rounds the target to a lower value.
FLOOR(C5,0.5/144) 0.236111 The rounded value in number format.
Convert to Time Format 5:40 AM The value is converted to time format and the rounded time to the last nearest 5 minutes.
• Drag down the Fill Handle to see the result in the rest of the cells.

## Related Articles

<< Go Back to Round Time in Excel | Rounding in Excel | Number Format | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Osman Goni Ridwan

OSMAN GONI RIDWAN is a marine engineer who loves exploring Excel and VBA programming. Programming, to him, is a time-saving tool for efficiently managing data, files, and internet tasks. He is proficient in Rhino3D, Maxsurf, C++, MS Office, AutoCAD, Excel, and VBA and goes beyond the basics. Armed with a B.Sc in Naval Architecture & Marine Engineering from BUET, OSMAN has transitioned into the role of a content developer. Specializing in creating technical content exclusively centered around Excel... Read Full Bio

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF