# Round Time to Nearest 5 Minutes in Excel (4 Quick Methods)

Get FREE Advanced Excel Exercises with Solutions!

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.

## 4 Easy Methods to Round Time to Nearest 5 Minutes in Excel

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. ### 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.

### 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 value 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 ## Related Articles #### Osman Goni Ridwan

I am Ridwan, graduated from Naval Architecture and Marine Engineering Dept, BUET, currently residing in Dhaka, Bangladesh. And my passion is to grow up my skillsets with industry demands. My prime goal is to be a data analyst as I do love to solve problems and play with data.

We will be happy to hear your thoughts Advanced Excel Exercises with Solutions PDF  