Rounding time values in Google Sheets can be complex, but functions like MROUND, CEILING, and FLOOR make it manageable. These functions allow you to round, round up, or round down hours, minutes, and seconds to specified intervals.
This guide provides detailed examples to help you round time values without needing extensive prior knowledge of each function.
Syntax and Functions Used
MROUND(value, factor)
: Rounds to the nearest specified interval.CEILING(value, factor)
: Rounds up to the nearest specified interval.FLOOR(value, factor)
: Rounds down to the nearest specified interval.
In all the examples below, the outputs are time values. Be sure to apply Format > Number > Time to the result cells for proper display.
Rounding Hours in Google Sheets
Round Hours
Using MROUND to round hours to the nearest hour:
- Example:
- Time in A1: 12:39:36
- Formula:
=MROUND(A1, "01:00:00")
- Result: 13:00:00
Round Up Hours
Using CEILING to always round hours up:
- Example:
- Time in A1: 12:39:36
- Formula:
=CEILING(A1, "01:00:00")
- Result: 13:00:00
Round Down Hours
Using FLOOR to always round hours down:
- Example:
- Time in A1: 12:39:36
- Formula:
=FLOOR(A1, "01:00:00")
- Result: 12:00:00
Rounding Minutes in Google Sheets
When rounding minutes, specify intervals like 5, 10, or 15 minutes. Here are examples of rounding to 15-minute intervals.
Round Minutes
Using MROUND to round to the nearest 15 minutes:
- Example:
- Time in A2: 12:39:36
- Formula:
=MROUND(A2, "00:15:00")
- Result: 12:45:00
Round Up Minutes
Using CEILING to always round minutes up to 15-minute intervals:
- Example:
- Time in A2: 12:39:36
- Formula:
=CEILING(A2, "00:15:00")
- Result: 12:45:00
Round Down Minutes
Using FLOOR to round down to the nearest 15-minute interval:
- Example:
- Time in A2: 12:39:36
- Formula:
=FLOOR(A2, "00:15:00")
- Result: 12:30:00
Rounding Seconds in Google Sheets
Specify intervals for rounding seconds, such as 10 or 15 seconds.
Round Seconds
Using MROUND to round seconds to the nearest 15 seconds:
- Example:
- Time in A3: 12:39:36
- Formula:
=MROUND(A3, "00:00:15")
- Result: 12:39:30
Round Up Seconds
Using CEILING to always round seconds up to the nearest 15-second interval:
- Example:
- Time in A3: 12:39:36
- Formula:
=CEILING(A3, "00:00:15")
- Result: 12:39:45
Round Down Seconds
Using FLOOR to always round seconds down to the nearest 15-second interval:
- Example:
- Time in A3: 12:39:36
- Formula:
=FLOOR(A3, "00:00:15")
- Result: 12:39:30
Summary
Rounding time values in Google Sheets can be simplified by using MROUND, CEILING, and FLOOR functions with specified intervals for hours, minutes, or seconds. This flexibility is helpful for tasks requiring standardized time intervals. Format cells using Format > Number > Time for accurate display.
Resources
- Converting Time Duration to Day, Hour, and Minute in Google Sheets
- How to Convert Military Time to Standard Time in Google Sheets
- How to Add Hours, Minutes, Seconds to Time in Google Sheets
- How to Increment Time by Minutes or Hours in Google Sheets
- How to Increment DateTime by One Hour in Google Sheets (Array Formula)
- Create Custom Time Slot Sequences in Google Sheets
- How to Extract Time from DateTime in Google Sheets: 3 Methods