0

Round off time in Duration format

Geae Albay

July 02, 2019 02:57

None

Follow

Is there a way we can round off the minutes of a duration format?
e.g 11:13, 11:23, 11:04 when rounded off will be: 11:15, 11:25, 11:05

• Facebook
• Twitter
• LinkedIn

2 comments

• 0
  
  Janice Janczyn July 03, 2019 19:23

  Hi Geae,

  Yes, first you need to convert these duration values (I assume your examples are in h:m format) to seconds using the TIME function, for example:

       TIME( ARRAY( "11:13,11:23, 1:04" ), "h:m" )

  Then divide by 60 to get the number of minutes, use the CEILING function to round forward to the next 5 minute mark and multiply by 60 to return to seconds units.

       CEILING( TIME( ARRAY( "11:13,11:23, 1:04" ), "h:m" ) / 60, 5 ) * 60

  On the Properties tab, set Format to duration, hh:mm.

  If instead you want to round up/down to the nearest 5 minute mark, use the ROUND function instead of CEILING and divide by 300 seconds instead of 60.

       ROUND( TIME( ARRAY( "11:13,11:23, 1:04" ), "h:m" ) / 300 ) * 300

  Happy dashboarding!

  Janice

• 0
  
  Geae Albay July 19, 2019 08:19

  Thanks Janice. It worked!
  
  Geae