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 5-minute 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 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() -
(... * 5)converts the rounded number of whole 5-minute 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 5-minute-rounded minute component from (6), and a zero second component. See also: CONCATENATE() -
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.
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 5-minute 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 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)converts the rounded number of whole 5-minute 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 5-minute-rounded minute component from (5), and a zero seconds component. See also: CONCATENATE() -
TIME(...)converts the Time-format Text value from (6) to a proper Time value. See also: TIME() -
... - "00:00:00"converts the hourless, 5-minute-rounded Time value from (7) to a Duration value. -
... + 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.
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 5-minute 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 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() -
(... * 5)converts the rounded number of whole 5-minute 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 5-minute-rounded minute component from (6), and a zero second component. See also: CONCATENATE() -
TIME(...)converts the Time-format 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 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.
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.
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?
