FAQ: Rounding DateTime, Duration, and Time values

[Steve]

• Round DateTime value to nearest 5 minutes
• Round Duration value to nearest 5 minutes
• Round Time value to nearest 5 minutes

[Steve]

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:

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.):

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

[Steve]

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:

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.):

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

[Suvrutt_Gurjar]

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.

[Steve]

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:

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.):

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

[Peter_Bernsen]

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?

[lubu08]

Thank you!

But it has error with case:
TIME(“xxx:60:00”) => return a blank result

Example:
[Clock in] = “12:09”
[Clock out] = “18:05”
=> TIME(“5:60:00”) => This entry is invalid

My solution:

if( isblank([Clock out]),
  time("0:0:00"),
  time(
    concatenate(
      floor(ceiling(totalminutes([Clock out]-[Clock in])/5)*5/60),
      ":",
      mod(ceiling(totalminutes([Clock out]-[Clock in])/5)*5,60),
      ":00"
    )
  )
)

[1minManager]

Awesome work @Steve . You probably saved me an hour or two here

One quick mod from me that might help out others. My use-case was I needed to round up a time to the nearest 15mins. But the formula initally wasn’t working - the result was blank. After stripping it down I found the reason. It was changing 16:47:00 to 16:60:00 then TIME() function wouldn’t interpret that. So I’ve added 2 IF() statements to add 1 to the hour and make the minutes 0 if the rounded up minutes equals 60.

TIME(
	CONCATENATE(
		IF(
			(CEILING(TOTALSECONDS(([Clock In] - "00:00:00")-HOUR([Clock In] - "00:00:00"))/ 60.0/ 15.0)*15)=60,
			HOUR([Clock In] - "00:00:00")+1,
			HOUR([Clock In] - "00:00:00")
		),
		":",
		IF(
			(CEILING(TOTALSECONDS(([Clock In] - "00:00:00")-HOUR([Clock In] - "00:00:00"))/ 60.0/ 15.0)*15)=60,
			0,
			(CEILING(TOTALSECONDS(([Clock In] - "00:00:00")-HOUR([Clock In] - "00:00:00"))/ 60.0/ 15.0)*15)
		),
		":0"
	)	
)

Hope this helps someone

[Josh_Klassen]

Okay, so I have tried to use this formula to round to the nearest 15 minute interval and for the most part it works. It just doesn't work the way I want when I am trying to get it to round to the next hour. For example if I input 12:55 it doesn't give an output it's just blank. I must be missing something.

TIME(
CONCATENATE(
HOUR([Time In] - "00:00:00"),
":",
(
ROUND(
TOTALSECONDS(
([Time In] - "00:00:00")
- HOUR([Time In] - "00:00:00")
)
/ 60.0
/ 15.0
)
* 15
),
":0"
)
)

In these attached images [New Time In] is the result of the formula taking values from [Time In]

[KGingeri]

Well all solutions here fall over for me at some point with some value for Duration!
I have spent HOURS on this, trying to round AND re-render the time (duration) properly.  Here is my final solution (15 minute round up)...

  IF(Number(Index(Split([Duration],":"),2))+14.0 > 59,
        Text(Number(Index(Split([Duration],":"),1))+1),
        Text(Number(Index(Split([Duration],":"),1)))
      )
  &IFS(
        Number(Index(Split([Duration],":"),2))+14.0 > 59, ":00",
        Number(Index(Split([Duration],":"),2))+14.0 > 44, ":45",
        Number(Index(Split([Duration],":"),2))+14.0 > 29, ":30",
        Number(Index(Split([Duration],":"),2))+14.0 > 14, ":15",
        Number(Index(Split([Duration],":"),2))+14.0 < 15, ":00"
      )

BTW, this result needs to go to a TEXT type field.

UPDATE!
See this post.  I forgot I could use MOD(). 😊