Rounding time to nearest 5 minutes in a dropdown

[jernejp]

Hi,

I have two dropdowns, where the user can select the hours and then minutes. 

I want to set default values to show current time. While I can do this with Hour(Now()), I can't figure out how to do it for minutes. The dropdown's Items = ["00"; "05"; "10"; "15"; "20"; "25"; "30"; "35"; "40"; "45"; "50"; "55"]

So - how would I do Minute(Now()) and round it down to nearest 5 minutes? 

So if the time is 13:24, it would round down to 13:20.

Thanks!

[timl · Accepted solution]

Hi @jernejp 

Maybe something like this could work for you:

If( Right(Text(Minute(Now())),1) = "5" || Right(Text(Minute(Now())),1) = "0", 
    Minute(Now()),
    Value(Right(Text(Minute(Now())),1)) > 0 && Value(Right(Text(Minute(Now())),1)) < 5,
    Left(Text(Minute(Now())),1) & "0",
    Value(Right(Text(Minute(Now())),1)) > 5 && Value(Right(Text(Minute(Now())),1)) < 10,
    Left(Text(Minute(Now())),1) & "5"
)

The If statement here does the following:

• If the right most character ends in 5 or 0, the minute value remains as it is.
• If the right most character is between 1 and 5, replace the right most character with 0.
• If the right most character is between 6 and 9, replace the right most character with 5.
  
  
  

I think this should do the trick, but maybe someone else here can come up with a more elegant solution.

[jernejp]

Thanks, it works! 

[Keneti]

I know it is an old tread but had the same question and found this, but I came up with another solution that I wanted to share.

For the items in the dropdown: (instead of [00,01,02,03,....] )

Hour dropdown items:        AddColumns(Sequence(24,0),"THours", Text(Value,"00"))
Minutes dropdown items:   AddColumns(Sequence(12,0,5),"TMinutes", Text(Value,"00"))

For the minute dropdown default value:

If(
     Minute(_time) > 57, "00",
     Value(Right(_time,1)) in [3,4,5,6,7], Concatenate(Left(Minute(_time),1),"5"),
     Round(Minute(_time),-1)
)

What it does is if minutes is over 57 it will return "00".
Else it will check if the last digit in minutes is in [3,4,5,6,7], if so it returns first digit in minutes concatenated with "5".
Else it will round minute to nearest 10.

_time is a variable that simply is a timer on my start screen that starts and goes through the whole app with pause set to false, duration 1000 ms and on timer start to Set(_Time, Now())
that way _time will always be the same as Now() and if you use the variable instead of Now() the dropdowns will change the default value over time instead of being rendered with Now() once as the screen loads.

For the hour dropdown default value:

If(Hour(_time) = 23 && Minute(_time) > 57, "00", If(Minute(_time) < 57, Text(Hour(_time),"00"), Text(Hour(_time) + 1),"00") )

If you use Hour(Now()) and the time is 15:58 the default values for the dropdowns will be 15:00, with this if statement it will round the hour up if minutes is 58 or higher.

hope this helps.