Tanjim Reza
Sometimes, it is very tough to manage the data when working with time data as they can be so infinitesimally different from each other. It would be easier and quicker for us to calculate and use the data if we could round up the time’s data. This article will show you 3 quick tricks with all the necessary formulas to round up time in Excel.
Download Practice Workbook
You can download our workbook from here and practice afterward.
What Is Rounding Time?
Rounding a number means raising up or down the number to the nearest integer value. Time data mainly contains hour, minute, and seconds values. Now, the time value might be so infinitesimally unique which makes it harder to visualize and time-consuming to calculate. At this time, you can round the time to its nearest integer hour, minute, or second value or even nearest to your desired value range like 0.5 or 5 or 10 and so on. Say, you have a time data as 8:27:23. Now, you can round it to 8:27:20 or 8:27:25 for a more common second value. You can round it to 8:25:00 or 8:30:00 for a more common minute value. Moreover, you can round it to 8:00:00 or 9:00:00 for a more common hour value.
Quick View to the Functions Used to Round Time
MROUND: The MROUND function rounds the time to the nearest integer value or nearest to your desired value range like 0.5 or 5 or 10 or so based on hour, minute, or second.
CEILING: The CEILING function rounds the time up to the nearest larger integer value or nearest to your desired value range like 0.5 or 5 or 10 or so based on hour, minute, or second.
FLOOR: The FLOOR function rounds the time down to the nearest smaller integer value or nearest to your desired value range like 0.5 or 5 or 10 or so based on hour, minute, or second.
3 Examples of How to Round Time in Excel
In our dataset, there are mainly two columns. The first column contains the accurate time data. And the second column contains the rounded time data. Follow this article below to learn about 3 handy approaches to round time in Excel using all the necessary formulas.
1. Round Time to the Nearest Hour
You can round your actual time to the closest hour value or you can round up your time data to a higher hour value and also round down your time data to a lower hour value.
1.1 Round Time to the Nearest Hour Value Using MROUND Function
Follow the steps below to round time to the nearest hour value. 👇
Steps:
- First, put an equal sign(=) in the cell where you want your rounded time.
- Following, write MROUND to enable the MROUND function. Now, refer to the B5 cell as you want to round this time value.
- Subsequently, write “1:00” in the place of the second argument. This “1:00” means the number will be rounded to the closest hour.
- Press the Enter button and you will see the time is rounded to its closest hour value.
- Now, you can drag the fill handle downward to copy the formula to all the cells below.
The Formula:
=MROUND(B5,"1:00")
- Alternatively, you can also use TIME(1,0,0) for rounding the time to the closest hour. Here, the TIME function’s first argument is the hour. That’s why we gave 1 in the first argument. Press the Enter button and you will see the time is rounded to its closest hour value. Now, you can drag the fill handle downward similarly to copy the formula to all the cells below.
The Formula:
=MROUND(B5,TIME(1,0,0))
- In the second argument, you can also use 1/24 for rounding the time to the closest hour. Here,1/24 means 1 hour and so the value will be rounded to the closest hour after pressing the Enter button. Now, you can drag the fill handle downward similarly to copy the formula to all the cells below.
The Formula:
=MROUND(B5,1/24)
The result will look like this. 👇
Read More: Round to Nearest 5 or 9 in Excel (8 Easy Methods)
1.2 Round-Up Time with CEILING Function
Now, you can round up your time to the larger closest hour. Follow the steps below to accomplish this. 👇
Steps:
- First, put an equal sign(=) in the cell where you want your rounded up time.
- Following, write CEILING to enable the CEILING function. Now, refer to the B5 cell as you want to round this time value.
- Subsequently, write “1:00” in the place of the second argument. This “1:00” means the number will be rounded to the larger closest hour. Press the Enter button and you will see the time is rounded to its larger closest hour value. Now, you can drag the fill handle downward similarly to copy the formula to all the cells below.
- In the second argument, you can also use TIME(1,0,0) for rounding up the time to the larger closest hour. Here, the TIME function’s first argument is the hour. That’s why we gave 1 in the first argument. Subsequently, press the Enter button and you will see the time is rounded to its larger closest hour value. Now, you can drag the fill handle downward to copy the formula to all the cells below.
- In the second argument, you can also use 1/24 for rounding up the time to the larger closest hour. Here,1/24 means 1 hour and so the value will be rounded up to the closest larger hour after pressing the Enter button. Now, you can drag the fill handle downward to copy the formula to all the cells below.
The result will look like this. 👇
Read More: How to Round to Nearest 10 Cents in Excel (4 Suitable Methods)
1.3 Round Down Time
Moreover, you can round down your time to the closest lesser hour value. Follow the steps below to accomplish this. 👇
Steps:
- First, put an equal sign(=) in the cell where you want your rounded-down time.
- Following, write FLOOR to enable the FLOOR function. Now, refer to the B5 cell as you want to round this time value.
- Subsequently, write “1:00” in the place of the second argument. This “1:00” means the number will be rounded down to the closest lesser hour value. Press the Enter button and you will see the time is rounded down to its closest lesser hour value. Now, you can drag the fill handle downward to copy the formula to all the cells below.
The Formula:
=FLOOR(B5,"1:00")
- In the second argument, you can also use TIME(1,0,0) for rounding down the time to the closest lesser hour. Here, the TIME function’s first argument is the hour. That’s why we gave 1 in the first argument. Subsequently, press the Enter button and you will see the time is rounded down to its closest lesser hour value. Now, you can drag the fill handle downward to copy the formula to all the cells below.
The Formula:
=FLOOR(B5,TIME(1,0,0))
- In the second argument, you can also use 1/24 for rounding down the time to the closest lesser hour value. Here,1/24 means 1 hour and so the value will be rounded down to the closest lesser hour after pressing the Enter button. Now, you can drag the fill handle downward to copy the formula to all the cells below.
The Formula:
=FLOOR(B5,1/24)
The result will look like this. 👇
Read More: How to Remove Decimals in Excel with Rounding (10 Easy Methods)
2. Round Time to the Nearest Minute
Similarly, you can round your actual time to the closest minute value or you can round up your time data to a higher rounded minute value and also round down your time data to a lower rounded minute value.
2.1 Round Time to the Nearest Minute Value
Say, we want to round time data to the nearest 10 minutes value. Follow the steps below to accomplish this. 👇
Steps:
- First, put an equal sign(=) in the cell where you want your rounded time.
- Following, write MROUND to enable the MROUND function. Now, refer to the B5 cell as you want to round this time value.
- Subsequently, write “0:10” in the place of the second argument. This “0:10” means the number will be rounded to the nearest 10 minutes value. Press the Enter button and you will see the time is rounded to its closest 10 minutes value. Now, you can drag the fill handle downward to copy the formula to all the cells below.
The Formula:
=MROUND(B5,"0:10")
- In the second argument, you can also use TIME(0,10,0) for rounding the time to the closest 10 minutes value. Here, the TIME function’s second argument is the minute. That’s why we gave 10 in the second argument. Press the Enter button and you will see the time is rounded to its closest 10 minutes value. Now, you can drag the fill handle downward similarly to copy the formula to all the cells below.
The Formula:
=MROUND(B5,TIME(1,10,0))
- In the second argument, you can also use 1/144(10/24*60) for rounding the time to the closest 10 minutes value. Here,1/144 means 10 minutes and so the value will be rounded to the closest 10 minutes value after pressing the Enter button. Now, you can drag the fill handle downward similarly to copy the formula to all the cells below.
The Formula:
=MROUND(B5,1/144)
The result will look like this. 👇
Read More: Round off Formula in Excel Invoice (9 Quick Methods)
2.2 Round-Up Time
You can also round up time in Excel to the larger 10 minutes value. Follow the steps below to accomplish this. 👇
Steps:
- First, put an equal sign(=) in the cell where you want your rounded time.
- Following, write CEILING to enable the CEILING function. Now, refer to the B5 cell as you want to round this time value.
- Subsequently, write “0:10” in the place of the second argument. This “0:10” means the number will be rounded to the nearest higher 10 minutes value. Press the Enter button and you will see the time is rounded to its closest higher 10 minutes value. Now, you can drag the fill handle downward to copy the formula to all the cells below.
The Formula:
=CEILING(B5,"0:10")
- In the second argument, you can also use TIME(0,10,0) for rounding the time to the closest higher 10 minutes value. Here, the TIME function’s second argument is the minute. That’s why we gave 10 in the second argument. Press the Enter button and you will see the time is rounded to its closest higher 10 minutes value. Now, you can drag the fill handle downward similarly to copy the formula to all the cells below.
The Formula:
=CEILING(B5,TIME(1,10,0))
- In the second argument, you can also use 1/144(10/24*60) for rounding the time to the closest higher 10 minutes value. Here,1/144 means 10 minutes and so the value will be rounded to the closest higher 10 minutes value after pressing the Enter button. Now, you can drag the fill handle downward similarly to copy the formula to all the cells below.
The Formula:
=CEILING(B5,1/144)
The result will look like this. 👇
Read More: Rounding to Nearest Dollar in Excel (6 Easy Ways)
2.3 Round Down Time
Moreover, you can round down time in Excel to the closest lesser 10 minutes value. Follow the steps below to accomplish this. 👇
Steps:
- First, put an equal sign(=) in the cell where you want your rounded time.
- Following, write FLOOR to enable the FLOOR function. Now, refer to the B5 cell as you want to round this time value.
- Subsequently, write “0:10” in the place of the second argument. This “0:10” means the number will be rounded to the nearest lesser 10 minutes value. Press the Enter button and you will see the time is rounded to its closest lesser 10 minutes value. Now, you can drag the fill handle downward to copy the formula to all the cells below.
The Formula:
=FLOOR(B5,"0:10")
- In the second argument, you can also use TIME(0,10,0) for rounding down the time to the closest lesser 10 minutes value. Here, the TIME function’s second argument is the minute. That’s why we gave 10 in the second argument. Press the Enter button and you will see the time is rounded to its closest lesser 10 minutes value. Now, you can drag the fill handle downward similarly to copy the formula to all the cells below.
The Formula:
=FLOOR(B5,TIME(0,10,0))
- In the second argument, you can also use 1/144(10/24*60) for rounding the time to the closest lesser 10 minutes value. Here,1/144 means 10 minutes and so the value will be rounded to the closest lesser 10 minutes value after pressing the Enter button. Now, you can drag the fill handle downward to copy the formula to all the cells below.
The result will look like this. 👇
Read More: Excel VBA: Round to Nearest 5 (Macro and UDF)
3. Round Time to the Nearest Second
Moreover, you can round time in Excel to the closest second value or you can round up your time data to a higher rounded second value and also round down your time data to a lower rounded second value.
3.1 Round Time to the Nearest Second Value
Say, we want to round time data to the nearest 10 seconds value. Follow the steps below to accomplish this. 👇
Steps:
- First, put an equal sign(=) in the cell where you want your rounded time.
- Following, write MROUND to enable the MROUND function. Now, refer to the B5 cell as you want to round this time value.
- Subsequently, write “0:0:10” in the place of the second argument. This “0:0:10” means the number will be rounded to the nearest 10 seconds value. Press the Enter button and you will see the time is rounded to its closest 10 seconds value. Now, you can drag the fill handle downward to copy the formula to all the cells below.
The Formula:
=MROUND(B5,"0:0:10")
- In the second argument, you can also use TIME(0,0,10) for rounding the time to the closest 10 seconds value. Here, the TIME function’s third argument is the second. That’s why we gave 10 in the third argument. Press the Enter button and you will see the time is rounded to its closest 10 seconds value. Now, you can drag the fill handle downward similarly to copy the formula to all the cells below.
The Formula:
=MROUND(B5,TIME(0,0,10))
- In the second argument, you can also use 10/86400(10/24*60*60) for rounding the time to the closest 10 seconds value. Here,10/86400 means 10 seconds and so the value will be rounded to the closest 10 seconds value after pressing the Enter button. Now, you can drag the fill handle downward to copy the formula to all the cells below.
The Formula:
=MROUND(B5,10/86400)
The result sheet will look like this. 👇
Read More: How to Add ROUND Formula to Multiple Cells in Excel (2 Easy Ways)
3.2 Round-Up Time
You can also round up time in Excel to the larger 10 seconds value. Follow the steps below to accomplish this. 👇
Steps:
- First, put an equal sign(=) in the cell where you want your rounded time.
- Following, write CEILING to enable the CEILING function. Now, refer to the B5 cell as you want to round this time value.
- Subsequently, write “0:0:10” in the place of the second argument. This “0:0:10” means the number will be rounded to the nearest larger 10 seconds value. Press the Enter button and you will see the time is rounded to its closest larger 10 seconds value. Now, you can drag the fill handle downward to copy the formula to all the cells below.
The Formula:
=CEILING(B5,"0:0:10")
- In the second argument, you can also use TIME(0,0,10) for rounding the time to the closest 10 seconds value. Here, the TIME function’s third argument is the second. That’s why we gave 10 in the third argument. Press the Enter button and you will see the time is rounded to its closest larger 10 seconds value. Now, you can drag the fill handle downward similarly to copy the formula to all the cells below.
The Formula:
=CEILING(B5,TIME(0,0,10))
- In the second argument, you can also use 10/86400(10/24*60*60) for rounding the time to the closest larger 10 seconds value. Here,10/86400 means 10 seconds and so the value will be rounded to the closest larger 10 seconds value after pressing the Enter button. Now, you can drag the fill handle downward to copy the formula to all the cells below.
The Formula:
=CEILING(B5,10/86400)
The result sheet will look like this. 👇
Read More: How to Roundup a Formula Result in Excel (4 Easy Methods)
3.3 Round Down Time
Moreover, you can round down time in Excel to the closest lesser 10 seconds value. Follow the steps below to accomplish this. 👇
Steps:
- First, put an equal sign(=) in the cell where you want your rounded time.
- Following, write FLOOR to enable the FLOOR function. Now, refer to the B5 cell as you want to round this time value.
- Subsequently, write “0:0:10” in the place of the second argument. This “0:0:10” means the number will be rounded to the nearest lesser 10 seconds value. Press the Enter button and you will see the time is rounded to its closest lesser 10 seconds value. Now, you can drag the fill handle downward to copy the formula to all the cells below.
The Formula:
=FLOOR(B5,"0:0:10")
- In the second argument, you can also use TIME(0,0,10) for rounding the time to the closest 10 seconds value. Here, the TIME function’s third argument is the second. That’s why we gave 10 in the third argument. Press the Enter button and you will see the time is rounded to its closest lesser 10 seconds value. Now, you can drag the fill handle downward similarly to copy the formula to all the cells below.
The Formula:
=FLOOR(B5,TIME(0,0,10))
- In the second argument, you can also use 10/86400(10/24*60*60) for rounding the time to the closest lesser 10 seconds value. Here,10/86400 means 10 seconds and so the value will be rounded to the closest lesser 10 seconds value after pressing the Enter button. Now, you can drag the fill handle downward to copy the formula to all the cells below.
The Formula:
=FLOOR(B5,10/86400)
The result sheet will look like this. 👇
Conclusion
So, here, I have shown you 3 easy methods to round time in Excel. You can follow any of these methods to round time. I hope you find this article helpful and informative. If you have any further queries or recommendations, please feel free to contact me. And, visit ExcelDemy for many more articles like this. Thank you!
