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)))``

Announcements

#### 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

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

#### 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.

#### 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)