cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
dennisb88
Resolver III
Resolver III

Filter gallery items

Hi all,

 

I've a rectangle that I will fill with a color based on if items are match with a specific time value.

What I want to achieve is the follow:

If one of my gallery items, that have date/time fields (called startdate and enddate) has the value 8:00 (startdate) and 9:00 (end date) the rectangle has the color purple. That part is working fine.

But it's not always working, for example when a item has the value: startdate: 07:00 & enddate: 08:00 It should be grey but it's purple.

I do this with the following logica in the fill property:

If(CountRows(Filter(gal_MondayTasks_2.AllItems,Hour('Startdate (startdate)') = 8 Or Hour('Startdate (startdate)')<=8,Hour('Enddate (enddate)')>=8 And Minute('Enddate (enddate)')>1 Or Hour('Startdate (startdate)')<=8,Hour('Enddate (enddate)')>=8 )) >=1,Purple,DimGrey) 

I need to achieve the following logica:

If startdate = 08:00 or earlier and/or enddate is 8:01 or later, set color to purple.

If enddate =08:00 set color to grey

 

At this moment it's working fine, except when a item has the enddate on 08:00, it should be grey, but now it's purple.

 

How can I achieve this?

 

Thanks in advance.

1 ACCEPTED SOLUTION

Accepted Solutions
dennisb88
Resolver III
Resolver III

I found how to fix it, this is the correct filter:

    Hour('Startdate (Startdate)') = 8 Or Hour('Startdate (Startdate)') <= 8,
    Hour('Enddate (enddate)') > 8 And Minute('Enddate (enddate)') > 1 Or Hour('Startdate (Startdate)') <= 8,
    Hour('Enddate (enddate)') > 8 Or Hour('Startdate (Startdate)') <= 8,
    Hour('Enddate (enddate)') >= 8 And Minute('Enddate (enddate)') >= 1 Or Hour('Startdate (Startdate)') = 8,
    Hour('Enddate (enddate)') >= 8

View solution in original post

4 REPLIES 4
zmansuri
Super User
Super User

If(CountRows(Filter(gal_MondayTasks_2.AllItems,Hour('Startdate (startdate)') = 8 Or Hour('Startdate (startdate)')<=8,Hour('Enddate (enddate)') = >8 And Minute('Enddate (enddate)')>1 Or Hour('Startdate (startdate)')<=8,Hour('Enddate (enddate)')>=8 )) >=1,Purple,DimGrey)

 

remove this bold =

 

The condition

Hour('Enddate (enddate)') = >8

turns true even when end date is 8. Which should be greater than 8 not greater than or equal to

Hi @zmansuri ,

 

Thank you for your reply.

I'm closer to the solution. It's working when the end time is 8.00, but when the end time is for example 8.30, it's not filled with purple where it should be purple.

 

So for example:

If start time is 8.00 > it should be purple (works fine)

If end time is 8.00 > it should be grey (Works fine)

If end time is for example 8.30 > it should be purple. (does not work).

 

My current formula:

If(CountRows(Filter(gal_MondayTasks_2.AllItems,Hour('Startdate (startdate)') = 8 Or Hour('Startdate (startdate)')<=8,Hour('Enddate (enddate)') >8 And Minute('Enddate (enddate)')>1 Or Hour('Startdate (startdate)')<=8,Hour('Enddate (enddate)')>8 )) >=1,Purple,DimGrey)

 

Any suggestion?

 

Thanks in advance!

dennisb88
Resolver III
Resolver III

Does anyone know how to filter this?

dennisb88
Resolver III
Resolver III

I found how to fix it, this is the correct filter:

    Hour('Startdate (Startdate)') = 8 Or Hour('Startdate (Startdate)') <= 8,
    Hour('Enddate (enddate)') > 8 And Minute('Enddate (enddate)') > 1 Or Hour('Startdate (Startdate)') <= 8,
    Hour('Enddate (enddate)') > 8 Or Hour('Startdate (Startdate)') <= 8,
    Hour('Enddate (enddate)') >= 8 And Minute('Enddate (enddate)') >= 1 Or Hour('Startdate (Startdate)') = 8,
    Hour('Enddate (enddate)') >= 8

View solution in original post

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Solution Authors
Top Kudoed Authors
Users online (2,567)