3 Ways to Round Off Time to Nearest Multiple of 5 Minutes in Excel

ByRhidi Barma Updated on Basic Excel

Microsoft Excel has multiple functions to round off numbers. Among them, the ROUND, MROUND, FLOOR, CEILING, etc. are some of the functions. In this tutorial, I will show you how to round off time to the nearest multiple of 5 minutes in Excel using the ROUND and MROUND functions.

ROUND Time to Nearest Multiple of 5 Minutes in Excel.xlsx

In the column, Timestamp, I have some random timestamps. In the column, Nearest 5 Minutes, I will use formulas to round all the timestamps to the nearest multiple of 5 minutes.

Dataset to round off time nearest multiple of 5 minutes in Excel

Round Off Time to Nearest Multiple of 5 Minutes with MROUND Function

Excel has a dedicated function to round off numbers to a certain multiple. The name of the function is MROUND. Now I will use the MROUND function to round off the timestamps to the nearest multiple of 5 minutes in Excel.

Syntax

=MROUND(number, multiple)

Formula

=MROUND(A2,"0:05")

Formula Explanation

According to the syntax of the MROUND function, cell A2 is the number. And “0:05” is multiple. The multiple specifies that the formula must return the nearest multiple of 5 minutes of the specified number i.e. 2:25 PM.

If you mention any other multiple other than 0:05, the formula will return the corresponding nearest multiple of the specified multiple.

Use the MROUND function to round off time to the nearest multiple of 5 minutes in Excel. So, follow these steps below:

  1. Select cell B2.
  2. Type the formula: =MROUND(A2,”0:05″)
  3. Press ENTER.
  4. Copy down the formula to cell B10 by dragging the lower-right corner of cell B2.

The MROUND function rounds off all the timestamps to their corresponding nearest multiple of 5 minutes.

For example, the timestamp 2:23 PM has two bracket timestamps that are multiple of 5 minutes. They are 2:20 PM and 2:25 PM. The timestamp 2:25 PM is closer to 2:23 PM than that of 2.20 PM. Thus, the formula has returned 2:25 PM in cell B2 instead of 2:20 PM.

Using MROUND Function to Round Off Time to the Nearest Multiple of 5 Minutes

Round Off Time to Nearest Multiple of 5 Minutes Using the ROUND Function

You can also use the ROUND function instead of the MROUND function. But you need to add some extra weights in order to calculate the nearest multiple of 5 minutes. Now let me show you how to round off time to the nearest multiple of 5 minutes.

Syntax

=ROUND(number, num_digits)

Formula

= (ROUND((A2*1440)/5, 0)*5)/1440

Formula Explanation

The formula used above is (ROUND((A2*1440)/5, 0)*5)/1440. We can rewrite the formula as (ROUND((A2*24*60)/5, 0)*5)/(24*60).

First, the time is converted to minutes to a multiple of 5 minutes. This is done by multiplying the time by 1440 and then dividing it by 5. Then the fraction is rounded off using the ROUND function. In the formula, 0 specifies that there will be no decimal places after the decimal point. Thus the ROUND function returns solid rounded minutes of the time in cell A2.

Finally, the rounded minutes are multiplied by 5 again and divided by 1440 to get back to the original time format again.

To round off the nearest multiple of 5 minutes in Excel using the ROUND function, go through these steps below:

  1. Click on an empty cell.
  2. Write the formula: = (ROUND((A2*1440)/5, 0)*5)/1440
  3. Press ENTER.
  4. Copy down the formula to cell B10 by dragging the lower-right corner of cell B2.

The above formula rounds off all the timestamps to their corresponding nearest multiple of 5 minutes.

For example, the timestamp 2:23 PM has two bracket timestamps that are multiple of 5 minutes. They are 2:20 PM and 2:25 PM. The timestamp 2:25 PM is closer to 2:23 PM than that of 2.20 PM.

Thus, the formula has returned 2:25 PM in cell B2 instead of 2:20 PM.

Using ROUND Function to Round Off Time to the Nearest Multiple of 5 Minutes

Round Off Time to Nearest Multiple of 5 Minutes with TIME, HOUR, MINUTE, & ROUND Functions

Finally, I will use a combination of the TIME, HOUR, MINUTE, & ROUND functions. This combination will return the rounded-off times to their nearest multiple of 5 minutes.

Syntax

=TIME(HOUR(serial_number),ROUND((MINUTE(serial_numbe)/60)*12,0)*5,0)

Formula

=TIME(HOUR(A2),ROUND((MINUTE(A2)/60)*12,0)*5,0)

Formula Explanation

Here, the main formula is made up of the TIME function. The syntax of the TIME function is: =TIME(hour, minute, second)

The main idea behind the formula is to work with hours, minutes, and seconds separately.

  • HOUR(A2) returns 14. But it is converted to 2 as the cell format is set to 12-Hour time format.
  • (MINUTE(A2)/60)*12 converts the minute part of the time in cell A2 to the multiple of 5
  • ROUND((MINUTE(A2)/60)*12,0) rounds off the minutes returned by (MINUTE(A2)/60)*12 to the nearest integer value.
  • The 0 at the end of the formula specifies that the second part is not considered in the formula.
  • Finally, the TIME function returns the converted time of cell A2 into the hour, minute, and second format.

Round off the nearest multiple of 5 minutes in Excel by combining TIME, HOUR, MINUTE, and ROUND functions. So, here’s how:

  1. Select a blank cell.
  2. Type the formula: =TIME(HOUR(A2),ROUND((MINUTE(A2)/60)*12,0)*5,0)
  3. Press ENTER.
  4. Copy down the formula to cell B10 by dragging the Fill Handle.

The formula converts all the timestamps to their nearest multiple of 5 minutes. To return the nearest multiple of the timestamps, sometimes the formula rounded up the times. Sometimes the formula is also rounded down the timestamps to calculate the nearest multiple of 5 minutes.

Using TIME, HOUR, MINUTE, & ROUND Functions

Conclusion

So, I have discussed 3 effective methods to round off time to the nearest multiple of 5 minutes in Excel. I hope you’ve found this article helpful. You can read more Excel articles from our website’s Blog page. Have a great workday!

Frequently Asked Questions

How do you round up time?

To round up time in Excel, use the following formula: =CEILING(A2,”0:15″)
Replace A2 with the cell containing the time value. This formula rounds the time up to the nearest 15-minute interval. The “0:15” argument specifies the interval to which you want to round.

What is the formula for 15-minute intervals in Excel?

Create 15-minute intervals in Excel with the formula: =ROUNDUP(A2*96,0)/96
Replace A2 with the cell containing the time. This formula rounds the time to the nearest 15-minute interval in Excel.

Related Articles

Rate this post

Data Analyst

Howdy! I'm Rhidi Barma. I'm a professional Excel user. I've completed my graduation from Jahangirnagar University, Bangladesh. I've been working with Microsoft Excel since 2015. I love writing blogs on MS Excel tips & tricks, data analysis, business intelligence, capital market, etc. I love to spend my leisure time reading books and watching movies. To know more about me, you can follow me on social media platforms. Thanks!

Leave a Reply

Your email address will not be published. Required fields are marked *