cancel
Showing results for
Did you mean: 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 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)))``

2 REPLIES 2  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. 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)))``   