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

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

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

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? 