cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Joko
Level: Powered On

Dropdown based on time

Afternoon, i have an idea, however not too sure if its possible.


I have a dropdown with the below code and would like it to auto select based on a time.  Is this possible? 

 

So P1 if the time is 9:00 - 10:00
P2 if the time is 10:00-11:00 and so on.

 

Table({Id: 1, Value: "P1"}, {Id: 2, Value: "P2"}, {Id: 3, Value: "Form"},{Id: 4, Value: "P3"},{Id: 5, Value: "P4"},{Id: 6, Value: "P5"},{Id: 7, Value: "After School"})

Thanks! 

2 ACCEPTED SOLUTIONS

Accepted Solutions
Joko
Level: Powered On

Re: Dropdown based on time

I have managed to sort this, so thought i would post it for anyone else trying this.

 

If(
    Hour(Now()) >= 08 && Minute(Now()) >= 40 && Hour(Now()) <= 09 && Minute(Now()) <= 45,
    "P1", If(
    Hour(Now()) >= 09 && Minute(Now()) >= 45 && Hour(Now()) <= 11 && Minute(Now()) <= 00,
    "P2", If(
    Hour(Now()) >= 11 && Minute(Now()) >= 00 && Hour(Now()) <= 11 && Minute(Now()) <= 30,
    "Tutorial", If(
    Hour(Now()) >= 11 && Minute(Now()) >= 30 && Hour(Now()) <= 12 && Minute(Now()) <= 30,
    "P3", If(
    Hour(Now()) >= 12 && Minute(Now()) >= 30 || Hour(Now()) <= 14 && Minute(Now()) <= 05,
    "P4",If(
    Hour(Now()) >= 2 && Minute(Now()) >= 05 && Hour(Now()) <= 15 && Minute(Now()) <= 05,
    "P5"
))))))

Might be abit rough and basic but it does the trick.

 

If anyone knows a cleaner way, that would be super! 

View solution in original post

Super User
Super User

Re: Dropdown based on time

Hi,

 

Yes a few quick things:

  • Try not to nest your If checks, the logic statement itself like Now>something is a check so doesn't need to be wrapped into another If and then saves you on figuring out closing brackets all over the place.
  • You need to be careful with logic bias with some of these as you've done them as one long && statement, so you will end up with it not assigning values correctly even though it may look correct to you:
    For example, your last check is looking at hours between 2AM(I think you meant to write 14, not 2) and 3PM and the minutes checks if they are <= 05 and >= 05, which is an impossible solution unless the time is exactly 02:05 03:05, 04:05 etc,
    and in this one
    Hour(Now()) >= 08 && Minute(Now()) >= 40 && Hour(Now()) <= 09 && Minute(Now()) <= 45,
    you have it checking hour is between 8 and 9, and minutes is between  40 and 45, so it will only trigger from 8:40-8:45 and 9:40-9:45
    - the same applies to most of the others
  • Anything after 15:05 will get no value set and will be blank as you have no Else catch at the end
  • You can simplify the checks you've done by doing date comparisons using DateTimeValue() and Now()

 

 

If(
   Now() >= DateTimeValue("08:40") && Now() < DateTimeValue("09:45"),
   "P1",
   Now() >= DateTimeValue("09:45") && Now() < DateTimeValue("11:00"),
   "P2",
   Now() >= DateTimeValue("11:00") && Now() < DateTimeValue("11:30"),
   "Tutorial",
   Now() >= DateTimeValue("11:30") && Now() < DateTimeValue("12:30"),
   "P3",
   Now() >= DateTimeValue("12:30") && Now() < DateTimeValue("14:05"),
   "P4",
   Now() >= DateTimeValue("14:05") && Now() <= DateTimeValue("15:05"),
   "P5",

   //else check

   "P5"
)

 

Confirmed working here:
image.png

 

Hope this helps!

ManCat




Don't forget to 'Mark as Solution' if a post answered your question and always 'Thumbs Up' the posts you like or helped you!

View solution in original post

4 REPLIES 4
Joko
Level: Powered On

Re: Dropdown based on time

I have managed to sort this, so thought i would post it for anyone else trying this.

 

If(
    Hour(Now()) >= 08 && Minute(Now()) >= 40 && Hour(Now()) <= 09 && Minute(Now()) <= 45,
    "P1", If(
    Hour(Now()) >= 09 && Minute(Now()) >= 45 && Hour(Now()) <= 11 && Minute(Now()) <= 00,
    "P2", If(
    Hour(Now()) >= 11 && Minute(Now()) >= 00 && Hour(Now()) <= 11 && Minute(Now()) <= 30,
    "Tutorial", If(
    Hour(Now()) >= 11 && Minute(Now()) >= 30 && Hour(Now()) <= 12 && Minute(Now()) <= 30,
    "P3", If(
    Hour(Now()) >= 12 && Minute(Now()) >= 30 || Hour(Now()) <= 14 && Minute(Now()) <= 05,
    "P4",If(
    Hour(Now()) >= 2 && Minute(Now()) >= 05 && Hour(Now()) <= 15 && Minute(Now()) <= 05,
    "P5"
))))))

Might be abit rough and basic but it does the trick.

 

If anyone knows a cleaner way, that would be super! 

View solution in original post

Super User
Super User

Re: Dropdown based on time

Hi,

 

Yes a few quick things:

  • Try not to nest your If checks, the logic statement itself like Now>something is a check so doesn't need to be wrapped into another If and then saves you on figuring out closing brackets all over the place.
  • You need to be careful with logic bias with some of these as you've done them as one long && statement, so you will end up with it not assigning values correctly even though it may look correct to you:
    For example, your last check is looking at hours between 2AM(I think you meant to write 14, not 2) and 3PM and the minutes checks if they are <= 05 and >= 05, which is an impossible solution unless the time is exactly 02:05 03:05, 04:05 etc,
    and in this one
    Hour(Now()) >= 08 && Minute(Now()) >= 40 && Hour(Now()) <= 09 && Minute(Now()) <= 45,
    you have it checking hour is between 8 and 9, and minutes is between  40 and 45, so it will only trigger from 8:40-8:45 and 9:40-9:45
    - the same applies to most of the others
  • Anything after 15:05 will get no value set and will be blank as you have no Else catch at the end
  • You can simplify the checks you've done by doing date comparisons using DateTimeValue() and Now()

 

 

If(
   Now() >= DateTimeValue("08:40") && Now() < DateTimeValue("09:45"),
   "P1",
   Now() >= DateTimeValue("09:45") && Now() < DateTimeValue("11:00"),
   "P2",
   Now() >= DateTimeValue("11:00") && Now() < DateTimeValue("11:30"),
   "Tutorial",
   Now() >= DateTimeValue("11:30") && Now() < DateTimeValue("12:30"),
   "P3",
   Now() >= DateTimeValue("12:30") && Now() < DateTimeValue("14:05"),
   "P4",
   Now() >= DateTimeValue("14:05") && Now() <= DateTimeValue("15:05"),
   "P5",

   //else check

   "P5"
)

 

Confirmed working here:
image.png

 

Hope this helps!

ManCat




Don't forget to 'Mark as Solution' if a post answered your question and always 'Thumbs Up' the posts you like or helped you!

View solution in original post

Joko
Level: Powered On

Re: Dropdown based on time

Thank you so much for this.  I was totally off.  

 

I am using this and its working.  I did however add a false on the last P5 one so that it would still fill the entry.

 

Thanks again!! 

Super User
Super User

Re: Dropdown based on time

You're welcome! 🙂




Don't forget to 'Mark as Solution' if a post answered your question and always 'Thumbs Up' the posts you like or helped you!

Helpful resources

Announcements
New Ranks and Rank Icons in April

'New Ranks and Rank Icons in April

Read the announcement for more information!

Better Together’ Contest Finalists Announced!

'Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

thirdimage

Community Summit North America

Innovate, Collaborate, Grow - The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors
Users online (4,302)