Rounding time is something tricky in Google Sheets. You can use the functions MROUND, CEILING and FLOOR to round, round up, round down hour, minute, second in Google Sheets respectively.
You can learn in this tutorial all about rounding time in Google Sheets. It’s not a must to learn the above functions individually to follow this tutorial. You can use the formulas given as examples in this tutorial as it’s.
How to Round, Round Up, Round Down Hour, Minute, Second in Google Sheets
Let’s start with rounding of Hours, then Minutes that followed by Seconds. Here we go.
Note:
1. Format the cells where you apply the formula to time from the menu Format > Number.
You May Also Like: Learn Google Sheets Time Functions
1. How to Round / Round Up / Round Down Hours in Google Sheets
1.A] Round Hours In Google Sheets:
Here I’m using Google Sheets MROUND function to ROUND the hours.
Time in Cell A1 is 12:39:36
=MROUND(A1,"01:00:00")
Result: 13:00:00
Time in Cell A2 is 06:29:39
=MROUND(A2,"01:00:00")
Result: 06:00:00
If you analyze these two formulas you can understand one thing. The rounding up has taken place if the minute is>=30. Else the formula actually retains the hour.
In other words, it’s just round not round up based on minutes. The below formula rounds up hours in Google Sheets.
1.B] Round-Up Hours in Google Sheets:
To Roundup in Google Sheets, you should use the function CEILING.
The value in A1 is 12:39:36
=CEILING(A1,"01:00:00")
Result: 13:00:00
Time value in A2 is 06:29:39
=CEILING(A2,"01:00:00")
Result: 07:00:00
Here you can see that the Ceiling formula rounds up the hours irrespective of the minutes. To further clarify, see one more example of rounding up hours in Google Sheets.
Time in A3: 06:00:01
=CEILING(A3,"01:00:00")
Result: 07:00:00
1.C] Round Down Hours in Google Sheets:
With the help of Google Sheets FLOOR function, you can round down hours in Google Sheets. Here is one example.
The value in Cell A1 is 12:39:36
=FLOOR(A5,"01:00:00")
Result: 12:00:00
2. How to Round / Round Up / Round Down Minutes in Google Sheets
Here there is one major difference. Here you should specify round, round up or round down to specific minutes like 1 minute, 2 minutes, 10 minutes, 20 minutes like any minutes. Please keep in mind that we are going to deal with minutes not hour. That’s why this specification is required. If you don’t understand, please check the examples below.
Here in the below example I’m rounding up / rounding down / rounding the minutes to 15 minutes. You can just change the minutes in the formulas if you want different rounding.
2.A] Round Minutes in Google Sheets:
For this example to round minutes, time in cell A2 is 12:39:36.
=MROUND(A2,"00:15")
Result: 12:45:00
Another example of the time in Cell A2 as 06:11:39.
=MROUND(A2,"00:15")
Result: 06:15:00
Change the 15 minutes to 30 and see the difference.
2.B] Round-Up Minutes in Google Sheets:
As I’ve mentioned, the function to round up time is Ceiling. Here I want to round up minutes. See different examples.
Time in cell A2 is 12:39:36.
=CEILING(A2,"00:15")
Result:12:45:00
Another example of the time value in Cell A2 as 06:11:39.
=CEILING(A2,"00:15")
Result: 06:15:00
In the above two examples, you may please note that rounding of the minute is based on 15 minutes. You can modify it by changing the minutes in the formulas above.
2.C] Round Down Minutes in Google Sheets:
Similar to hours, you can use the FLOOR function to round down minutes in Google Sheets.
Examples:
Time in cell A2 is 12:39:36.
=FLOOR(A2,"00:15")
Result:12:30:00
Example with time in Cell A2: 06:11:39.
=FLOOR(A2,"00:15")
Result: 06:00:00
Here also the rounding down is taking place based on 15 minutes.
More Tutorials: Learn Complete Date Functions in Google Sheets
3. How to Round / Round Up / Round Down Seconds in Google Sheets
Similar to rounding of minutes, here also you should specify round, round up or round down to 10 seconds / 20 seconds or any specific seconds value.
3.A] Round Seconds in Google Sheets:
To round seconds in Google Sheets, you can use the function MROUND.
Example: 1
The value in A1 is 12:39:36
=MROUND(A1, "00:00:15")
Result: 12:39:30
Example: 2
Time in A2: 06:11:39
=MROUND(A2, "00:00:15")
Result: 06:11:45
3.B] Round Up Seconds in Google Sheets:
Needless to say, Round Up seconds requires Ceiling function.
The value in Cell A2: 12:39:36
=CEILING(A2, "00:00:15")
Result: 12:39:45
The value in Cell A2: 06:11:08
=CEILING(A2, "00:00:15")
Result: 06:11:15
If you want to round up the seconds to minute in Google Sheets, just change the seconds in the formula as below.
Time in A2: 06:11:14
=CEILING(A2, "00:00:60")
Result: 06:12:00
3.C] Round Down Seconds in Google Sheets:
Finally, let me explain how to round down seconds in Google Sheets. With this, I’m winding up this tutorial on the topic Round, Round Up, Round Down Hour, Minute, Second in Google Sheets.
The value in Cell A2: 12:39:36
=FLOOR(A2, "00:00:15")
Result: 12:39:30
That’s all.
