# 3 Ways to 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.

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.

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

- Select cell
**B2**. - Type the formula:
**=MROUND(A2,”0:05″)** - Press
**ENTER**. - 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.**

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

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

- Click on an empty cell.
- Write the formula:
**= (ROUND((A2*1440)/5, 0)*5)/1440** - Press
**ENTER**. - 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.**

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

- Select a blank cell.
- Type the formula:
**=TIME(HOUR(A2),ROUND((MINUTE(A2)/60)*12,0)*5,0)** - Press
**ENTER**. - 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.

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

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