The dataset contains a column with time. To round the time to the nearest 5 minutes:
Method 1 – Using the ROUND Function
Use 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.
Read More: How to Round Time to Nearest Minute in Excel
Method 2 – Using the MROUND Function
Use 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
Use the CEILING function to round the value to the next whole number.
- 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
Use the FLOOR function to round the time to the nearest last 5 minutes.
- 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.
Download Practice Workbook
Download the practice workbook.
Related Articles
<< Go Back to Round Time in Excel | Rounding in Excel | Number Format | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
