FAQ: Rounding DateTime, Duration, and Time values

AppSheet Tips & Tricks
1
10 Likes
2

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"
  )
)
6 Likes
3

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])
)
9 Likes
4

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"
  )
)
6 Likes
5

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
6

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?

3X_c_9_c9d08d194e5bcafae5c552c107d6da869cdc49f2.png
3X_c_9_c9d08d194e5bcafae5c552c107d6da869cdc49f2.png537Ă—497 14.3 KB

7

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

3 Likes
8

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"
    )
  )
)
9

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”
    )
    )

time.PNG
time.PNG421Ă—468 10.6 KB

time2.PNG
time2.PNG414Ă—329 4.38 KB

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

10

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