# [3 Ways] Round Off Time to Nearest Multiple of 5 Minutes in 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.

### Introduction to the Dataset

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.

## Easiest Way to Round Off Time to the Nearest Multiple of 5 Minutes

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

**Usage Guide**

**Step_1:** Type the formula in cell **B2**.

**Step_2: **Press **ENTER**.

**Step_3:** Copy down the formula to cell **B10 **by dragging the lower-right corner of cell **B2**.

**Final Result**

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

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

**2 Ways to Round to Nearest 50 Cents in Excel****4 Formulas to Round Off Prices To Nearest 10 Cents in Excel**

### Alternative Way #1: 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

**Usage Guide**

**Step_1:** Type the formula in cell **B2**.

**Step_2: **Press **ENTER**.

**Step_3:** Copy down the formula to cell **B10 **by dragging the lower-right corner of cell **B2**.

**Final Result**

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

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

### Alternative Way #2: Using 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)

**Usage Guide**

**Step_1:** Type the formula in cell **B2**.

**Step_2: **Press **ENTER**.

**Step_3:** Copy down the formula to cell **B10 **by dragging the **Fill Handle**.

**Final Result**

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.

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

## Conclusion

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

**Related Articles**

**How to Round Off Numbers in Excel (9 Methods)****3 Formulas for Rounding Time to the Nearest Hour in Excel****[3 Formulas] Round Off Time to Nearest Multiple of 15 Minutes in Excel****5 Ways to Round a Formula with SUM Function in Excel****2 Ways to Stop Excel from Rounding up Percentages****How to Stop Excel from Rounding 16-Digit Numbers (2 Methods)**