Rounding Time in Excel to Nearest Hour: 6 Easy Methods

Method 1 – Rounding Time to Nearest Hour Using Excel ROUND Function

Steps:

• Make a column for rounding time to the hour and type the formula in cell D5.

The formula here uses the ROUND Function to convert the time of cell C5 to the nearest hour. In Excel, 1 hour equals 1/24 and we multiply 24 with the cell value. We don’t want any decimal value, so we set num_digits as 0. The formula is divided by 24 to convert it to hours.

• Press ENTER and you will see the time in cell C5 rounded to its nearest hour, which is 4:00:00 PM.

• Use the Fill Handle to AutoFill the lower cells. This operation will round all the times in the dataset to their nearest hours.

You can round time to the nearest hour using the ROUND Function.

Method 2 – Implementing MROUND Function to Round Time in Excel

Steps:

• Make a column for rounding time to the hour and type the formula in cell D5.

The formula here uses the MROUND function to convert the time of cell C5 to the nearest hour. In Excel, 1 hour equals 1/24 and that’s why we set the multiple as 1/24.

• Press ENTER and you will see the time in cell C5 rounded to its nearest your, which is 4:00:00 PM.

• Use the Fill Handle to AutoFill the lower cells. This operation will round all the times in the dataset to their nearest hours.

Thus you can round time to the nearest hour using the MROUND Function.

Method 3 – Using Excel TIME Functions to Round Time 

Steps:

• Make a column for rounding time to hour and type the formula in cell D5.

Here, we present the time of cell C5 with hours, minutes and seconds with the help of TIME Function.  As we only want the nearest hour, we set minute and second to 0. As we want the nearest value of hour, we put a logical test to see whether MINUTE(C5)+SECOND(C5) is greater than 30. If that’s true, then 1 hour is added to the value of HOUR(C5). In this case, MINUTE(C5)+SECOND(C5) equals 13 which is less than 30 so the output will be 9:00 AM.

• Press ENTER and you will see the time in cell C5 rounded to it’s nearest hour, which is 9:00:00 AM.

• Use the Fill Handle to AutoFill the lower cells. This operation will round all the times in the dataset to their nearest hours.

Thus you can round time to the nearest hour using the TIME Functions.

Method 4 – Rounding Time to Nearest Hour Using Excel FLOOR Function

Steps:

• Make a column for rounding time to hour and type the formula in cell D5.

The formula here uses the FLOOR Function to convert the time of cell C5 to the hour. In Excel, 1 hour equals 1/24 and that’s why we set the significance as 1/24.. It only converts the time to its hour.

• Press ENTER and you will see the time in cell C5 rounded to its hour which is 9:00:00 AM.

• Use the Fill Handle to AutoFill the lower cells. This operation will round all the times in the dataset to their hours.

Thus you can round time to the hour using the FLOOR Function.

Method 5 – Applying ROUNDDOWN Function to Round Time in Excel

Steps:

• Make a column for rounding time to hour and type the formula in cell D5.

The formula here uses the ROUNDDOWN Function to convert the time of cell C5 to the hour. In Excel, 1 hour equals 1/24 and that’s why we multiply 24 with the cell value. We don’t want any decimal value, so we set num_digits as 0. The whole formula is divided by 24 to convert it to hours.

• Press ENTER and you will see the time in cell C5 rounded to its nearest hour which is 9:00:00 AM.

• Use the Fill Handle to AutoFill the lower cells. This operation will round all the times in the dataset to their nearest hours.

Thus you can round time to hour using the ROUNDDOWN Function.

Method 6 – Implementing Excel CEILING Function to Round Time

Steps:

• Make a column for rounding time to hour and type the following formula in cell D5.

The formula here uses the CEILING Function to convert the time difference between cells D5 & C5 to an hour. In Excel, 1 hour equals 1/24 and that’s why we multiply 24 with the cell value. The significance is set as 1.

• Press ENTER and you will see the output in cell D5.

• Use the Fill Handle to AutoFill the lower cells.

You can round time difference to hour using the CEILING Function.

Download Practice Workbook

Related Article

• How to Round Time to Nearest Minute in Excel

<< Go Back to Round Time in Excel | Rounding in Excel | Number Format | Learn Excel