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

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.


Download Practice Workbook

You can download the practice workbook from here:


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.

Round Time to Nearest 5 minutes


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.

Round Time to Nearest 5 minutes

  • Then, the Format Cells window will open. After that, go to Number > Time and select any time formats. At last, press OK.

Round Time to Nearest 5 minutes

  • Now, paste the formula into the first cell D5.
=ROUND(C5*(24*60/5),0)/(24*60/5)

Round Time to Nearest 5 minutes

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.

Round Time to Nearest 5 minutes

  • Now, you have the column filled. And your time values are now rounded to the nearest 5 minutes.

Read More: How to Roundup a Formula Result in Excel (4 Easy Methods)


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")

Round Time to Nearest 5 minutes

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.

Using MROUND Function

  • Now, you will get the rounded value to the nearest 5 minutes mark.

Read More: How to Round to Nearest 10 Cents in Excel (4 Suitable Methods)


Similar Readings


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)

Using MROUND Function

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.

Read More: Rounding to Nearest Dollar in Excel (6 Easy Ways)


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)

Rounding Time Up to Its Next 5-Minute Mark Using Floor Function

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

Rounding Time Up to Its Next 5-Minute Mark Using Floor Function

Read More: Round off Formula in Excel Invoice (9 Quick Methods)


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

Osman Goni Ridwan

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

Leave a reply

ExcelDemy
Logo