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

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.

## Rounding Time to the Nearest Hour in Excel with MROUND Function

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

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

If you want to round time to the nearest hour in Excel with **MROUND**, follow these steps below:

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

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

## Rounding Time to the Nearest Hour in Excel 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

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

To round time to the nearest hour in Excel with the **ROUND** function, go through these steps below:

- Select a cell
**B2**.

- Type the formula:
**=ROUND(A2*24,0)/24** - 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 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**.

## Rounding Time to the Nearest Hour in Excel with TIME, HOUR, MINUTE, & SECOND Functions

In this method, 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)

**Formula Explanation**

This Excel formula adjusts a given time value in cell A2 by evaluating whether its minute component, plus the second component converted to minutes, exceeds 30 minutes. If this condition is true, it increments the hour component by 1; otherwise, it retains the original hour value. The resulting time is then formatted using the **TIME** function, setting the minutes and seconds to 0. Essentially, the formula rounds up the time to the next hour if the initial time is more than 30 minutes, producing a modified time value with zero minutes and seconds.

Here are the steps for rounding time to the nearest hour in Excel combining **TIME**, **HOUR**, **MINUTE** & **SECOND**:

- Select an empty cell.
- Type the formula:
**=TIME(HOUR(A2) +((MINUTE(A2)+SECOND(A2)/60)>30),0,0)** - Press
**ENTER**. - Copy down the formula to cell
**B10**by dragging the**Fill Handle**.

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.

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

## Frequently Asked Questions

### Can we round off time?

Yes, in Excel, you can round off time using the ROUND function. For example, to round a time value in cell **A1** to the nearest minute, you can use the formula** ROUND(A1, “00:01”)**. This formula rounds the time to the nearest minute, and you can adjust the “00:01” part to round to different time intervals.

### What is the rounding formula in Excel?

The rounding formula in Excel is** ROUND(cell_reference, num_digits)**. This formula rounds the value in the specified cell reference to the number of digits specified. For example, **ROUND(A1, 2)** would round the value in cell **A1** to two decimal places.

### What is the formula for hourly time in Excel?

The formula to calculate hourly time in Excel is **=HOUR(A1) + MINUTE(A1)/60**. This formula extracts the hour component from the time in cell **A1** and adds the minute component divided by **60**, effectively converting the minutes into decimal hours.

**Related Articles**

**How to Round Off Numbers in Excel (9 Methods)****[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)****[3 Ways] Round Off Time to Nearest Multiple of 5 Minutes in Excel**