FAQ: Rounding DateTime, Duration, and Time values

8 Likes

Round Time value to nearest 5 minutes

TIME(
  CONCATENATE(
    HOUR([Time] - "00:00:00"),
    ":",
    (
      ROUND(
        TOTALSECONDS(
          ([Time] - "00:00:00")
          - HOUR([Time] - "00:00:00")
        )
        / 60.0
        / 5.0
      )
      * 5
    ),
    ":0"
  )
)
Breakdown
  1. ([Time] - "00:00:00") converts the Time column value to a Duration value, reflecting the time past midnight. See also: Date and Time Expressions

  2. (... - HOUR(...)) removes the hour component from the Duration value computed by (1), leaving only minutes and seconds. See also: HOUR()

  3. TOTALSECONDS(...) converts the Duration value from (2) into a count of total seconds past the hour represented in the original Time column value. See also: TOTALSECONDS()

  4. (... / 60.0 / 5.0) converts the count of seconds from (3) into the equivalent number of minutes (... / 60.0), then that into the equivalent number of 5-minute intervals (... / 5.0). Decimal values are used to preserve fractional components to allow accurate rounding in (5), below. See also: Math Expressions

  5. ROUND(...) rounds the Decimal number of 5-minute intervals from (4) to the nearest whole number (a Number value) of 5-minute intervals past the hour represented in the original Time column value. See also: ROUND()

  6. (... * 5) converts the rounded number of whole 5-minute intervals from (5) into the equivalent number of minutes.

  7. CONCATENATE(..., ":", ..., ":0") constructs a Text value in Time value format comprised of the hour component of the original Time column value, the 5-minute-rounded minute component from (6), and a zero second component. See also: CONCATENATE()

  8. TIME(...) converts the Time-format Text value from (7) to a proper Time value. See also: TIME()

Customize

Replace Time with your own column name in each of the following places:

image

You may instead replace [Time] with any expression that produces a Time value. Each occurrence must be the same expression.

Replace 5 with your preferred interval, in whole minutes (10, 15, 30, etc.):

image

The chosen interval must be more than 0 and less than 60.

3 Likes

Round Duration value to nearest 5 minutes

(
  TIME(
    CONCATENATE(
      "0:",
      (
        ROUND(
          TOTALSECONDS(
            [Duration]
            - HOUR([Duration])
          )
          / 60.0
          / 5.0
        )
        * 5
      ),
      ":0"
    )
  )
  - "00:00:00"
  + HOUR([Duration])
)
Breakdown
  1. ([Duration] - HOUR([Duration])) removes the hour component from the Duration column value, leaving only minutes and seconds. See also: HOUR()

  2. TOTALSECONDS(...) converts the Duration value from (1) into a count of total seconds beyond the hours represented in the original Duration column value. See also: TOTALSECONDS()

  3. (... / 60.0 / 5.0) converts the count of seconds from (2) into the equivalent number of minutes (... / 60.0), then that into the equivalent number of 5-minute intervals (... / 5.0). Decimal values are used to preserve fractional components to allow accurate rounding in (4), below. See also: Math Expressions

  4. ROUND(...) rounds the Decimal number of 5-minute intervals from (3) to the nearest whole number (a Number value) of 5-minute intervals beyond the hours represented in the original Duration column value. See also: ROUND()

  5. (... * 5) converts the rounded number of whole 5-minute intervals from (5) into the equivalent number of minutes.

  6. CONCATENATE("0:", ..., ":0") constructs a Text value in Time value format comprised of a zero hours component, the 5-minute-rounded minute component from (5), and a zero seconds component. See also: CONCATENATE()

  7. TIME(...) converts the Time-format Text value from (6) to a proper Time value. See also: TIME()

  8. ... - "00:00:00" converts the hourless, 5-minute-rounded Time value from (7) to a Duration value.

  9. ... + HOUR([Duration]) adds the hours count of the original Duration column value into the hourless, 5-minute-rounded Duration value from (8).

Customize

Replace Duration with your own column name in each of the following places:

image

You may instead replace [Duration] with any expression that produces a Duration value. Each occurrence must be the same expression.

Replace 5 with your preferred interval, in whole minutes (10, 15, 30, etc.):

image

The chosen interval must be more than 0 and less than 60.

4 Likes

Round DateTime value to nearest 5 minutes

(
  DATETIME(DATE([DateTime]))
  + (
    TIME(
      CONCATENATE(
        HOUR(TIME([DateTime]) - "00:00:00"),
        ":",
        (
          ROUND(
            TOTALSECONDS(
              (TIME([DateTime]) - "00:00:00")
              - HOUR(TIME([DateTime]) - "00:00:00")
            )
            / 60.0
            / 5.0
          )
          * 5
        ),
        ":0"
      )
    )
    - "00:00:00"
  )
)
Breakdown
  1. (TIME([DateTime]) - "00:00:00") converts the DateTime column value to a Time value (TIME(...)), then to a Duration value, reflecting the time past midnight of the date in the original DateTime column value. See also: Date and Time Expressions, TIME()

  2. (... - HOUR(...)) removes the hour component from the Duration value computed by (1), leaving only minutes and seconds. See also: HOUR()

  3. TOTALSECONDS(...) converts the Duration value from (2) into a count of total seconds past the hour represented in the original DateTime column value. See also: TOTALSECONDS()

  4. (... / 60.0 / 5.0) converts the count of seconds from (3) into the equivalent number of minutes (... / 60.0), then that into the equivalent number of 5-minute intervals (... / 5.0). Decimal values are used to preserve fractional components to allow accurate rounding in (5), below. See also: Math Expressions

  5. ROUND(...) rounds the Decimal number of 5-minute intervals from (4) to the nearest whole number (a Number value) of 5-minute intervals past the hour represented in the original DateTime column value. See also: ROUND()

  6. (... * 5) converts the rounded number of whole 5-minute intervals from (5) into the equivalent number of minutes.

  7. CONCATENATE(..., ":", ..., ":0") constructs a Text value in Time value format comprised of the hour component of the original DateTime column value, the 5-minute-rounded minute component from (6), and a zero second component. See also: CONCATENATE()

  8. TIME(...) converts the Time-format Text value from (7) to a proper Time value.

  9. (... - "00:00:00") converts the Time value from (8) to a Duration value that can be used to adjust the DateTime value computed in (10), below.

  10. DATETIME(DATE([DateTime])) converts the original DateTime column value into a Date value (DATE([DateTime])), then converts that bare Date value back into a DateTime value, effectively removing the Time component of the original DateTime column value. See also: DATE(), DATETIME()

  11. (DATETIME(...) + (TIME(...) - ...)) constructs the desired DateTime value from the Date-only DateTime value from (10) (DATETIME(...)) and the 5-minute-rounded Duration value from (9) ((TIME(...) - ...)).

Customize

Replace DateTime with your own column name in each of the following places:

image

You may instead replace [DateTime] with any expression that produces a DateTime value. Each occurrence must be the same expression.

Replace 5 with your preferred interval, in whole minutes (10, 15, 30, etc.):

image

The chosen interval must be more than 0 and less than 60.

3 Likes

Hi @Steve,

Very useful that you have made both the duration and time expressions with customizable intervals - that will make it useful for all use cases of rounding of these. Date , time and duration are in general more challenging in any system to handle as these do not follow typical decimal math but 60-60-24 etc.

Thank you very much.

6 Likes

Thanks for this solution.
Sometimes I run into an error
In Berekentijd (English Calculated time) I use this form what you made:

(
  TIME(
    CONCATENATE(
      "0:",
      (
        ROUND(
          TOTALSECONDS(
            [Gewerkte uren]
            - HOUR([Gewerkte uren])
          )
          / 60.0
          / 15.0
        )
        * 15
      ),
      ":0"
    )
  )
  - "00:00:00"
  + HOUR([Gewerkte uren])
 - SWITCH([Pauze],"Werkplaats","001:00:00","Onderweg","000:30:00","000:00:00")
 )

Mostly I have no issues and somtimes it gives: NaN:NaN:NaN

What to do?

image