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