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
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,246)