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

([Time]  "00:00:00")
converts the Time column value to a Duration value, reflecting the time past midnight. See also: Date and Time Expressions 
(...  HOUR(...))
removes the hour component from the Duration value computed by (1), leaving only minutes and seconds. See also: HOUR() 
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() 
(... / 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 5minute intervals (... / 5.0
). Decimal values are used to preserve fractional components to allow accurate rounding in (5), below. See also: Math Expressions 
ROUND(...)
rounds the Decimal number of 5minute intervals from (4) to the nearest whole number (a Number value) of 5minute intervals past the hour represented in the original Time column value. See also: ROUND() 
(... * 5)
converts the rounded number of whole 5minute intervals from (5) into the equivalent number of minutes. 
CONCATENATE(..., ":", ..., ":0")
constructs a Text value in Time value format comprised of the hour component of the original Time column value, the 5minuterounded minute component from (6), and a zero second component. See also: CONCATENATE() 
TIME(...)
converts the Timeformat 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.
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

([Duration]  HOUR([Duration]))
removes the hour component from the Duration column value, leaving only minutes and seconds. See also: HOUR() 
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() 
(... / 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 5minute intervals (... / 5.0
). Decimal values are used to preserve fractional components to allow accurate rounding in (4), below. See also: Math Expressions 
ROUND(...)
rounds the Decimal number of 5minute intervals from (3) to the nearest whole number (a Number value) of 5minute intervals beyond the hours represented in the original Duration column value. See also: ROUND() 
(... * 5)
converts the rounded number of whole 5minute intervals from (5) into the equivalent number of minutes. 
CONCATENATE("0:", ..., ":0")
constructs a Text value in Time value format comprised of a zero hours component, the 5minuterounded minute component from (5), and a zero seconds component. See also: CONCATENATE() 
TIME(...)
converts the Timeformat Text value from (6) to a proper Time value. See also: TIME() 
...  "00:00:00"
converts the hourless, 5minuterounded Time value from (7) to a Duration value. 
... + HOUR([Duration])
adds the hours count of the original Duration column value into the hourless, 5minuterounded 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.
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

(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() 
(...  HOUR(...))
removes the hour component from the Duration value computed by (1), leaving only minutes and seconds. See also: HOUR() 
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() 
(... / 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 5minute intervals (... / 5.0
). Decimal values are used to preserve fractional components to allow accurate rounding in (5), below. See also: Math Expressions 
ROUND(...)
rounds the Decimal number of 5minute intervals from (4) to the nearest whole number (a Number value) of 5minute intervals past the hour represented in the original DateTime column value. See also: ROUND() 
(... * 5)
converts the rounded number of whole 5minute intervals from (5) into the equivalent number of minutes. 
CONCATENATE(..., ":", ..., ":0")
constructs a Text value in Time value format comprised of the hour component of the original DateTime column value, the 5minuterounded minute component from (6), and a zero second component. See also: CONCATENATE() 
TIME(...)
converts the Timeformat Text value from (7) to a proper Time value. 
(...  "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. 
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() 
(DATETIME(...) + (TIME(...)  ...))
constructs the desired DateTime value from the Dateonly DateTime value from (10) (DATETIME(...)
) and the 5minuterounded 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.
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 606024 etc.
Thank you very much.
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?