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

Calculating which shift is working to change fill color of calendar gallery date

Hi,

 

I am building a PowerApps for my departments holidays, I have been using Excel and I have a formula to determine which shift is working on each date as we are on a rolling 4 on 4 off shift pattern.

 

The formula I have been using is =IF(MOD((D100-DATE(2020,3,12))/8,1)<0.5,"Shift 1","Shift 2") - where D100 references a cell with the date in it, this was then copied across all relevant cells.

 

I have created a calendar gallery and what would like to do is change the color of the fill in a gallery depending on what shift is working on each date of the month.

 

Is this possible in PowerApps?

Any help would be much appreciated

 

Regards,

 

Damian  

1 ACCEPTED SOLUTION

Accepted Solutions
Damo10
Frequent Visitor

Hi,

I think that I have managed to work it out, I have the below code in the Fill property in the gallery.

It was not as straight forward as using the same formula from Excel as there were issues where it was calculating some blocks as 5 days instead of 4.

 

Switch(Dropdown1.Selected.Value,1,If(ThisItem.Value=Today(),ColorFade(Yellow,80%),If(Or(Round(Mod((ThisItem.Value-Date(2020,3,12))/8,1)/0.1,0)<5,Round(Mod((ThisItem.Value-Date(2020,3,12))/8,1)/0.1,0)=10),LightCyan,LightGray)),2,If(ThisItem.Value=Today(),ColorFade(Yellow,80%),If(Or(Round(Mod((ThisItem.Value-Date(2020,3,12))/8,1)/0.1,0)<5,Round(Mod((ThisItem.Value-Date(2020,3,12))/8,1)/0.1,0)=10),LightGray,LightGreen)))

 

View solution in original post

2 REPLIES 2
anasurielnh
Microsoft
Microsoft

Hello @Damo10 

 

Yes, this is possible. You can have a look at the TemplateFill property for your gallery and use a formula such as

 

If(ThisItem.Status = "ShiftA", Green, Transparent)

 

 

Let me know if this helps.

Community Support Team - Ana Navarro

If this post helps, please consider accepting it as the solution to help other members find it more quickly.

Damo10
Frequent Visitor

Hi,

I think that I have managed to work it out, I have the below code in the Fill property in the gallery.

It was not as straight forward as using the same formula from Excel as there were issues where it was calculating some blocks as 5 days instead of 4.

 

Switch(Dropdown1.Selected.Value,1,If(ThisItem.Value=Today(),ColorFade(Yellow,80%),If(Or(Round(Mod((ThisItem.Value-Date(2020,3,12))/8,1)/0.1,0)<5,Round(Mod((ThisItem.Value-Date(2020,3,12))/8,1)/0.1,0)=10),LightCyan,LightGray)),2,If(ThisItem.Value=Today(),ColorFade(Yellow,80%),If(Or(Round(Mod((ThisItem.Value-Date(2020,3,12))/8,1)/0.1,0)<5,Round(Mod((ThisItem.Value-Date(2020,3,12))/8,1)/0.1,0)=10),LightGray,LightGreen)))

 

Helpful resources

Announcements
Microsoft 365 Conference – December 6-8, 2022

Microsoft 365 Conference – December 6-8, 2022

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Difinity Conference 2022

Difinity Conference 2022

Register today for two amazing days of learning, featuring intensive learning sessions across multiple tracks, led by engaging and dynamic experts.

European SharePoint Conference

European SharePoint Conference

The European SharePoint Conference returns live and in-person November 28-December 1 with 4 Microsoft Keynotes, 9 Tutorials, and 120 Sessions.

Power Apps Ideas

Check out the New Ideas Site

We are excited to announce a new way to share your ideas for Power Apps!

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