# 3 Formulas for Rounding Time to the Nearest Hour in Excel

Microsoft Excel has multiple functions for rounding times. Among them, the **ROUND**, **MROUND**, **FLOOR**, **CEILING**, etc. are some of the functions. In this tutorial, I will help you with 3 formulas for rounding time to the nearest hour in Excel.

### Introduction to the Dataset

In the column, **Timestamp**, I have some random timestamps. In the column, **Nearest Hour**, I will use formulas for rounding all the timestamps to their nearest hours.

## Easiest Way of Rounding Time to the Nearest Hour

Excel has a dedicated function for rounding times to a certain multiple. The name of the function is **MROUND**. Now I will use the **MROUND **function to round off the timestamps to their nearest hours in Excel.

**Syntax**

=MROUND(number, multiple)

**Formula**

=MROUND(A2,"1:00")

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

For example, the timestamp** 2:25:23 PM** has two bracket timestamps that are the nearest whole hours. They are **2:00:00 PM** and **3:00:00 PM**. The timestamp **2:00:00 PM** is closer to** 2:25:23 PM** than that of **3.00:00 PM.**

Thus, the formula has returned **2:00:00 PM** in cell **B2 **instead of **3:00:00 PM.**

**Formula Explanation**

According to the syntax of the **MROUND **function, cell **A2 **is the number. And **“1:00”** is multiple. The multiple specifies that the formula must return the nearest hour i.e.** 2:00:00 PM.**

If you mention any other multiple other than **1:00**, the formula will return the corresponding nearest multiple of the specified hour.

### Alternative Way #1: Using 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 hours. Now let me show you how to round off time to the nearest hour.

**Syntax**

=ROUND(number, num_digits)

**Formula**

=ROUND(A2*24,0)/24

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

For example, the timestamp** 2:25:23 PM** has two bracket timestamps that are the nearest whole hours. They are **2:00:00 PM** and **3:00:00 PM**. The timestamp **2:00:00 PM** is closer to** 2:25:23 PM** than that of **3.00:00 PM.**

Thus, the formula has returned **2:00:00 PM** in cell **B2 **instead of **3:00:00 PM.**

**Formula Explanation**

The formula used above is **ROUND(A2*24,0)/24. **

First, the time is converted to hours. This is done by multiplying the time by **24.** 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 hours of the time in cell **A2. **

Finally, the rounded hours are divided by **24 **to get back to the original time format again.

### Alternative Way #2: Using TIME, HOUR, MINUTE, & SECOND Functions

Finally, I will combine the **TIME**, **HOUR**, **MINUTE**, and **SECOND **functions. This combination will return the rounded-off times to their nearest hours.

**Syntax**

=TIME(HOUR(time) +((MINUTE(time)+SECOND(time)/60)>30),0,0)

**Formula**

=TIME(HOUR(A2) +((MINUTE(A2)+SECOND(A2)/60)>30),0,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 hours. To return the nearest hours, sometimes the formula rounded up the timestamps. Sometimes the formula is also rounded down the timestamps to calculate the nearest hours.

**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)**returns the minute part of the time in cell**A2**.**SECOND(A2)/60**returns the second part of the time in cell**A2**.**(MINUTE(A2)+SECOND(A2)/60)>30**returns**TRUE**if the summation of**MINUTE(A2)**and**SECOND(A2)/60)**are greater than**30**. Otherwise, it returns**FALSE**.**HOUR(A2) +((MINUTE(A2)+SECOND(A2)/60)>30)**when**(MINUTE(A2)+SECOND(A2)/60)>30**returns**TRUE**, one extra hour is added to the hour returned by**HOUR(A2).**But when**(MINUTE(A2)+SECOND(A2)/60)>30**returns**FALSE, 0**is added to the hour returned by**HOUR(A2).**- The two
**zeros (0)**at the end of the formula convert the minutes and seconds to zeros in the final time format. - Finally, the
**TIME**function returns the converted time of cell**A2**into the hour, minute, and second format.

## Conclusion

So, I have discussed **3** formulas for rounding time to the nearest hour 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!