cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
SteveBouten
Frequent Visitor

Create calendar gallery to show only specific day - then fill cell if date exists in SharePoint list

Good morning,

 

I'm trying to create a calendar/gallery that only shows the Fridays of the month and then checks a SharePoint list to see if the date exists (created column), then fill it with green or red.

 

Every Friday of the month, an inspection has to be done. This gallery will then show if they missed an inspection date.

 

I can manually put labels in with the Fridays, but I want it to automatically update each year with the correct Fridays. Is this possible?

 

Thanks.

Kind regards,

1 ACCEPTED SOLUTION

Accepted Solutions
JR-BejeweledOne
Super User
Super User

You can.

 

Start by setting this in your App Onstart:   This will get the date for the first Friday of the year.

 

Set(varFirstFriday, Text(DateAdd( DateValue("1" & "/" & "1" & "/" & Year(Today())), (6- Weekday(DateValue("1" & "/" & "1" & "/" & Year(Today())))))))

 

Next create a collection.  This will be all the Fridays for the entire year.

ClearCollect(Fridays, 
   ForAll(Sequence(52, Value(Trim(Last(FirstN(Split(varFirstFriday, "/"), 2)).Result)), 7),  Date(Year(Today()), 1, Value))
)

 

Set your Gallery Items property to filter by the current month:

 

Filter(

Fridays, (Trim(Last(FirstN(Split(Text(Value), "/"), 1)).Result)) = Text(Month(Today()))

)

 

 

 

 

 

 

 

 



If you like this post, give it a Thumbs up. If it answered your question, Mark it as a Solution to enable other users find it.

View solution in original post

4 REPLIES 4
JR-BejeweledOne
Super User
Super User

You can.

 

Start by setting this in your App Onstart:   This will get the date for the first Friday of the year.

 

Set(varFirstFriday, Text(DateAdd( DateValue("1" & "/" & "1" & "/" & Year(Today())), (6- Weekday(DateValue("1" & "/" & "1" & "/" & Year(Today())))))))

 

Next create a collection.  This will be all the Fridays for the entire year.

ClearCollect(Fridays, 
   ForAll(Sequence(52, Value(Trim(Last(FirstN(Split(varFirstFriday, "/"), 2)).Result)), 7),  Date(Year(Today()), 1, Value))
)

 

Set your Gallery Items property to filter by the current month:

 

Filter(

Fridays, (Trim(Last(FirstN(Split(Text(Value), "/"), 1)).Result)) = Text(Month(Today()))

)

 

 

 

 

 

 

 

 



If you like this post, give it a Thumbs up. If it answered your question, Mark it as a Solution to enable other users find it.

View solution in original post

Hi,

Thanks! 

I seem to have a date format issue, we use "DD/MM/YYYY".

 

If I put the collection in a gallery I see "DD/MM/YYYY". But when I use your filter nothing show, unless I add "[$-en-US]00" or "[$-en-GB]00" in the format text field. Then I get a result, but it's 04/06/2021. It then shows the 4th day of June.

 

I can't seem to get the formatting right.

JR-BejeweledOne
Super User
Super User

 

Try changing your filter.    The (Trim(Last(FirstN(Split(Text(Value), "/"), 1) was getting the first value from the table created by the split, which is the month if your format is dd/mm/yyyy and using it to compare to the Month(today()) which returns the numerical value of the current month, in this case '4'.  

 

Since you are using a DD/MM/YYYY format, you want the second result from the split table.

 

 

Filter(

Fridays, (Trim(Last(FirstN(Split(Text(Value), "/"), 2)).Result)) = Text(Month(Today()))

)

 

 

 

 



If you like this post, give it a Thumbs up. If it answered your question, Mark it as a Solution to enable other users find it.

That resolved it, thanks a lot!

Helpful resources

Announcements
PA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

secondImage

Power Apps Community Call

Please join us on Wednesday, October 20th, at 8a PDT. Come and learn from our amazing speakers!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

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

Top Solution Authors
Top Kudoed Authors
Users online (1,483)