cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jernejp
Helper IV
Helper IV

Rounding time to nearest 5 minutes in a dropdown

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!

1 ACCEPTED SOLUTION

Accepted Solutions
timl
Super User
Super User

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.

 

View solution in original post

3 REPLIES 3
timl
Super User
Super User

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.

 

Thanks, it works! 

Keneti
New Member

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.

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Carousel_PP_768x460_Wave2 (1).png

2022 Release Wave 2 Plan

Power Platform release plan for the 2022 release wave 2 describes all new features releasing from October 2022 through March 2023.

365 EduCon 768x460.png

Microsoft 365 EduCon

Join us for two optional days of workshops and a 3-day conference, you can choose from over 130 sessions in multiple tracks and 25 workshops.

Top Solution Authors
Users online (5,873)