How to Round Time in Excel (With 3 Examples)

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.

MROUND Function

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.

CEILING Function

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.

FLOOR Function


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.

Sample Dataset


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

Use the MROUND Function and Quoted Hour Value

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

Use the MROUND & TIME Function to round Time in Excel

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

Use the MROUND Function and Hour Ratio

The result will look like this. 👇

Use the MROUND Function to Round Time in Excel

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.

Use the CEILING Function & Quoted Hour Value

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

Use the CEILING & TIME Function to Round Time in Excel

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

Use the CEILING Function and Hour Ratio

The result will look like this. 👇

Use the CEILING Function to Round Time in Excel

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

Use the FLOOR Function & Quoted Hour Value

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

Use the FLOOR & TIME Function to Round Time in Excel

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

Use the FLOOR Function & Hour Ratio

The result will look like this. 👇

Use the FLOOR Function to Round Time in Excel

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

Use the MROUND Function & Quoted Minute Value

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

Use the MROUND & TIME Function

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

Use the MROUND Function & Minute Ratio

The result will look like this. 👇

Use the MROUND Function to Round Time in Excel

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

Use the CEILING Function & Quoted Minute Value

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

Use the CEILING Function & TIME Function

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

Use the CEILING Function & Minute Ratio

The result will look like this. 👇

Use CEILING Function to Round Up Time in Excel

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

Use the FLOOR Function & Quoted Minutes Value

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

Use the FLOOR & TIME Function

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

Use the FLOOR Function & Minute Ratio

The result will look like this. 👇

Use the FLOOR Function to Round Down Time

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

Use the MROUND Function & Quoted Seconds Value

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

Use the MROUND & TIME Function to Round Time in Excel

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

Use the MROUND & Second Ratio

The result sheet will look like this. 👇

Use the MROUND Function to Round Time

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

Use the CEILING Function & Quoted Second Value

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

Use the CEILING & TIME Function to Round Time in Excel

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

Use the CEILING Function & Second Ratio

The result sheet will look like this. 👇

Use the CEILING Function to Round Up Time

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

Use the FLOOR Function & Quoted Seconds Value

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

Use the FLOOR & TIME Function to Round Time in Excel

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

Use the FLOOR Function & Seconds Ratio

The result sheet will look like this. 👇

Use the FLOOR Function to Round Down Time


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!


Related Articles

Tanjim Reza

Tanjim Reza

Hello! I am Md. Tanjim Reza Tanim. I have just completed my B.Sc from Naval Architecture & Marine Engineering Department, BUET. Currently, I am working as an Excel & VBA content developer. I always had a great fascination with Microsoft Excel and its cool functions and formulas. Here, I am learning every day about new functions and formulas and working on applying MS Excel to the analysis of our real-life problems. I have great enthusiasm for learning any kind of new things, writing articles, and solving real-life problems.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo