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
Power Apps News & Annoucements carousel

Power Apps News & Announcements

Keep up to date with current events and community announcements in the Power Apps community.

Power Apps Community Blog Carousel

Power Apps Community Blog

Check out the latest Community Blog from the community!

Top Solution Authors
Top Kudoed Authors
Users online (3,230)