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!