Rounding time in Google Sheets can be a bit tricky, but don’t worry, I’m here to help! In this tutorial, I’ll show you how to use the MROUND, CEILING, and FLOOR functions to round, round up, and round down hours, minutes, and seconds in Google Sheets. You don’t need to be an expert in these functions to follow along. I’ll provide easy-to-understand examples that you can use right away.
How to Round, Round Up, Round Down Hour, Minute, Second in Google Sheets
Let’s start with rounding hours, then move on to minutes and seconds. Buckle up, because we’re about to dive in.
Note: Before we get started, make sure to format the cells where you’ll apply the formulas as time by going to Format > Number.
1. How to Round / Round Up / Round Down Hours in Google Sheets
1.A] Round Hours In Google Sheets:
To round hours in Google Sheets, we’ll use the MROUND function. Let’s take a look at an example:
=MROUND(A1, "01:00:00")
Result: 13:00:00
In this example, the time in cell A1 is 12:39:36. The formula rounds it up to the nearest hour.
1.B] Round-Up Hours in Google Sheets:
To round up hours in Google Sheets, we’ll use the CEILING function. Here’s an example:
=CEILING(A1, "01:00:00")
Result: 13:00:00
In this case, the formula rounds up the hours of the time in cell A1, regardless of the minutes.
1.C] Round Down Hours in Google Sheets:
To round down hours in Google Sheets, we’ll use the FLOOR function. Here’s an example:
=FLOOR(A1, "01:00:00")
Result: 12:00:00
In this example, the formula rounds down the hours of the time in cell A1.
2. How to Round / Round Up / Round Down Minutes in Google Sheets
When it comes to rounding minutes, there’s one crucial difference. We need to specify the exact number of minutes we want to round to (e.g., 1 minute, 5 minutes, 15 minutes). Let’s explore some examples:
2.A] Round Minutes in Google Sheets:
To round minutes in Google Sheets, we’ll use the MROUND function. Here’s an example:
=MROUND(A2, "00:15")
Result: 12:45:00
In this example, the formula rounds the minutes of the time in cell A2 to the nearest 15-minute interval.
2.B] Round-Up Minutes in Google Sheets:
To round up minutes in Google Sheets, we’ll use the CEILING function. Here’s an example:
=CEILING(A2, "00:15")
Result: 12:45:00
In this case, the formula rounds up the minutes of the time in cell A2 based on a 15-minute interval.
2.C] Round Down Minutes in Google Sheets:
To round down minutes in Google Sheets, we’ll use the FLOOR function. Here’s an example:
=FLOOR(A2, "00:15")
Result: 12:30:00
In this example, the formula rounds down the minutes of the time in cell A2 to the nearest 15-minute interval.
3. How to Round / Round Up / Round Down Seconds in Google Sheets
Similar to rounding minutes, we need to specify the exact number of seconds to round to. Let’s explore some examples:
3.A] Round Seconds in Google Sheets:
To round seconds in Google Sheets, we’ll use the MROUND function. Here’s an example:
=MROUND(A1, "00:00:15")
Result: 12:39:30
In this example, the formula rounds the seconds of the time in cell A1 to the nearest 15-second interval.
3.B] Round Up Seconds in Google Sheets:
To round up seconds in Google Sheets, we’ll use the CEILING function. Here’s an example:
=CEILING(A2, "00:00:15")
Result: 12:39:45
In this example, the formula rounds up the seconds of the time in cell A2 based on a 15-second interval.
3.C] Round Down Seconds in Google Sheets:
To round down seconds in Google Sheets, we’ll use the FLOOR function. Here’s an example:
=FLOOR(A2, "00:00:15")
Result: 12:39:30
In this example, the formula rounds down the seconds of the time in cell A2 to the nearest 15-second interval.
And that’s it! You now know how to round, round up, and round down hours, minutes, and seconds in Google Sheets. Feel free to experiment with different intervals and formulas to suit your needs. For more tutorials and tips on Google Sheets, visit Crawlan.com!
Happy rounding!
