Generally, we use Excel to input and store our regular data. So, often we have to input time in Excel. Also, we often need to round time to the nearest 5 minutes or any other figures to analyze the data in Excel. It will help to make analytics and visualization. Here, this article will show how you can round time to the nearest 5 minutes in Excel with some easy methods and formulas.
How to Round Time to Nearest 5 Minutes in Excel: 4 Easy Methods
Suppose, you have a dataset with a column containing time. But, the values are in the exact value so you want to round the time to the nearest 5 minutes. Here, in this article, I am showing you 4 easy formulas to round the time.
1. Using ROUND Function
Here, we will use the ROUND function to round the time to the nearest 5 minutes mark. For this, we have to make a formula that will make the Excel understand the value of 5 minutes. As in Excel, it counts all formats as numbers. Time and dates are also numbers. The steps with clear explanation are given below:
STEPS:
- At first, you have to make the cells of the 2 columns named Time and Rounded in the Time format.
- For this, select the cells of these columns and go to Home > Number, and press on the arrow icon.
- Then, the Format Cells window will open. After that, go to Number > Time and select any time formats. At last, press OK.
- Now, paste the formula into the first cell D5.
=ROUND(C5*(24*60/5),0)/(24*60/5)
Formula Explanation
| 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. Which uses this formula: =(5*60+43)/(24*60) |
| (24*60/5) | 288.00 | It will make the full day of 24 Hours into 288 parts which are of 5 minutes each. |
| C5*(24*60/5) | 68.64 | It calculates how much parts of 5 minutes are spent by the time 5:43 PM |
| ROUND(C5*(24*60/5),0) | 69.00 | It will round the value 68.64 to its nearest integer 69. |
| ROUND(C5*(24*60/5),0)/(24*60/5) | 0.239583333 | Dividing 69 by 288 will give the spent time in number format. |
| In Time Format | 5:45:00 AM | Now, format it to time again and you will get the nearest time rounded to 5 minutes |
- Then, drag the Fill Handle icon to copy and paste the formula to the other cells.
- Now, you have the column filled. And your time values are now rounded to the nearest 5 minutes.
Read More: How to Round Time to Nearest Minute in Excel
2. Using MROUND Function
Importantly, using the MROUND function is simpler than the ROUND function. In this function, you will round the value in time format Hour : Minute : Second. Here, I am showing the steps with clear explanations and illustrations.
STEPS:
- Paste this formula in the first cell D5 of the column.
=MROUND(C5,"0:05")
Formula Explanation
| Step | Value After the Step | Explanation |
|---|---|---|
| Time | 5:43 AM | |
| C5 | It is the cell that contains the time value | |
| 0:05 | The time format is Hour:Minute: Second. As we want to round the value to the nearest 5 minutes so will take “00:05” in the multiple values. | |
| MROUND(C5,”0:05″) | 5:45 AM | Now, it round the minute value to the nearest multiple of 5 |
- At last, drag the Fill Handle button to copy and paste the formula to the other cells.
- Now, you will get the rounded value to the nearest 5 minutes mark.
Read More: How to Round Time to Nearest 15 Minutes in Excel
3. Round Time Up to Its Next 5-Minute Mark Using CEILING Function
Here, we will use the CEILING function to round the time. But, this function round the value to the next whole number which is a multiple of a given value. So, it will round to the higher time, no matter whether the nearest 5 minutes is lower or higher. Here, I am showing the steps with proper illustrations and explanations below.
- First, paste this formula into cell D5.
=CEILING(C5,0.5/144)
Formula Explanation
| Step | Value After the Step | Explanation |
|---|---|---|
| Time | 5:43 AM | This is the value of time which will be rounded |
| Time in Number Format | 0.238322 | This is the value of time in number format. Which uses this formula: =(5*60+43)/(24*60) |
| C5 | It is the cell that contains the time value | |
| 0.5/144 | 0.003472 | This is the amount of significance. The ceiling function will round the target to a next higher value of it and which is a multiple of this value. |
| CEILING(C5,0.5/144) | 0.239583 | This is the rounded value in number format. |
| Convert to Time Format | 5:45 AM | Now, This value will be converted to the time format and you will get the rounded time to its nearest 5 minutes up mark. |
- Now, use the Fill Handle icon to drag. So, the formula will come to other cells of the column. And, all the values will be rounded to the next 5 minutes mark.
4. Round Time Down to Its Last 5-Minute Mark Using FLOOR Function
Here, I will show how to use the FLOOR function to round the time to the nearest 5 minutes mark. But the FLOOR function will round the value down to the lower 5 minutes mark no matter whether the value is close to the higher value or not. Now, I will show you the steps to use the FLOOR function to round the time down to the last 5 minutes mark.
STEPS:
- First, Paste this formula into the first cell C5 of the column.
=FLOOR(C5,0.5/144)
Formula Explanation
| Step | Value After the Step | Explanation |
|---|---|---|
| Time | 5:43 AM | |
| Time in Number Format | 0.238322 | This is the value of time in number format. Which uses this formula: =(5*60+43)/(24*60) |
| C5 | It is the cell that contains the time value | |
| 0.5/144 | 0.003472 | This is the amount of significance. The floor function will round the target to a lower value of it and which is multiple of this value. |
| FLOOR(C5,0.5/144) | 0.236111 | This is a rounded value in number format. |
| Convert to Time Format | 5:40 AM | Now, This value will be converted to the time format and you will get the rounded time to its nearest 5 minutes up mark. |
- Now, use the Fill Handle icon to copy and paste the formula to the other cells
Download Practice Workbook
You can download the practice workbook from here:
Conclusion
In this article, I have tried to show you how to round time to the nearest 5 minutes in Excel. You can use the first 2 methods or the ROUND or MROUND function to get the nearest 5 minutes mark. And, using the third method or by the CEILING formula, you will get the next 5 minutes mark. And by the 4th method or by using the FLOOR function, you will get the last 5 minutes mark. I hope you found this article helpful. You can visit our website ExcelDemy to learn more Excel-related content. Please, drop comments, suggestions, or queries if you have any in the comment section below.
Related Articles
- Rounding Time in Excel to Nearest Hour
- How to Round Time in Excel
- Rounding Time to Nearest Quarter Hour in Excel
- How to Round Numbers in Excel Without Formula
<< Go Back to Round Time in Excel | Rounding in Excel | Number Format | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
