The article will show you some processes of rounding time to the nearest hour in Excel. Sometimes you may need to know the nearest hour of a certain time to simplify your calculation. We can use Excel as a tool for this purpose.
In the dataset, we have In an Out time of some employees in an organization.
Rounding Time in Excel to Nearest Hour: 6 Ways
1. Rounding Time to Nearest Hour Using Excel ROUND Function
In this section, I’ll show you how to use the ROUND function to round time to the nearest hours. We will be working on the Out Time of the employees. Let’s have a look at the procedure below.
Steps:
- Make a column for rounding time to hour and type the following formula in cell D5.
=ROUND(C5*24,0)/24
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 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 the ENTER button 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.
Thus you can round time to the nearest hour using the ROUND Function.
Read More: How to Add ROUND Formula to Multiple Cells in Excel
2. Implementing MROUND Function to Round Time in Excel
Another way of rounding time to the nearest hour is to use the MROUND function. Here, we will also be working on the Out Time. Let’s go through the process below.
Steps:
- Make a column for rounding time to hour and type the following formula in cell D5.
=MROUND(C5,1/24)
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 the ENTER button 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.
Thus you can round time to the nearest hour using the MROUND Function.
Read More: How to Round to Nearest 10 Cents in Excel
3. Using Excel TIME Functions to Round TimeÂ
In this section, I’ll show you how to use the combination of TIME Functions to round time to the nearest hours. The TIME Functions are TIME, HOUR, MINUTE and SECOND. We will be working on the In Time of the employees. Let’s have a look at the procedure below.
Steps:
- Make a column for rounding time to hour and type the following formula in cell D5.
=TIME(HOUR(C5) +((MINUTE(C5)+SECOND(C5)/60)>30),0,0)
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 the ENTER button 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.
Read More: How to Round Down to Nearest Whole Number in Excel
Similar Readings
- How to Remove Decimals in Excel with Rounding
- How to Round a Formula with SUM in Excel
- How to Round Excel Data to Make Summations Correct
4. Rounding Time to Nearest Hour Using Excel FLOOR Function
Another way of rounding time to the nearest hour is to use the FLOOR function. Here, we will also be working on In Time. Let’s go through the process below.
Steps:
- Make a column for rounding time to hour and type the following formula in cell D5.
=FLOOR(C5,1/24)
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 the ENTER button 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.
Read More: How to Round to Nearest Whole Number in Excel
5. Applying ROUNDDOWN Function to Round Time in Excel
In this section, I’ll show you how to use the ROUNDDOWN Function to round time to the hours. We will be working on the In Time of the employees. This function will round the time to it’s current hour. Let’s have a look at the procedure below.
Steps:
- Make a column for rounding time to hour and type the following formula in cell D5.
=ROUNDDOWN((C5)*24,0)/24
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 the ENTER button 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.
Read More: Round off Formula in Excel Invoice
6. Implementing Excel CEILING Function to Round Time
If you want to find out the time difference in hours, you can use the CEILING function. Let’s go through the process below.
Steps:
- Make a column for rounding time to hour and type the following formula in cell D5.
=CEILING((D5-C5)*24,1)
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 the ENTER button and you will see the output in cell D5.
- Use the Fill Handle to AutoFill the lower cells.
Thus you can round time difference to hour using the CEILING Function.
Read More: Rounding to Nearest Dollar in Excel
Practice Section
Here, I’m giving you the dataset that we used in this article so that you can practice these methods on your own.
Download Practice Workbook
Conclusion
In the end, I can assume that you can have the knowledge of rounding time in Excel to the nearest hour. This can be helpful to simplify your calculation. If you have any ideas or feedback regarding this article, please share them in the comment box. Because your valuable thoughts will inflate my knowledge and thus help me enrich my upcoming articles.
Related Articles
- How to Set Decimal Places in Excel with Formula
- How to Roundup a Formula Result in Excel
- Round to Nearest 5 or 9 in Excel
- How to Round Numbers to the Nearest Multiple of 5 in Excel
- Excel VBA: Round to Nearest 5
- Round Down to Nearest 10 in Excel
- How to Round to Nearest 100 in Excel
- How to Round Numbers to Nearest 10000 in Excel
- How to Round up Decimals in Excel
- How to Round Up to 2 Decimal Places in Excel
- How to Get 2 Decimal Places Without Rounding in Excel
- How to Round Percentages in Excel
- How to Set Rounding Precision in Excel
- How to Round Time in Excel
- How to Round Time to Nearest Minute in Excel
- How to Round Time to Nearest 15 Minutes in Excel
- How to Round Off to Nearest 50 Cents in Excel
- How to Stop Rounding in Excel
- How to Stop Excel from Rounding Large Numbers
- How to Remove Decimals Without Rounding in Excel
- How to Stop Excel from Rounding Up Decimals
- How to Round a Multiplication Formula in Excel
- How to Round Numbers in Excel Without Formula
