Fill the color based on days

[sindhureddy]

Hi,
Here in the below gallery i want to color the rows which have Sat,Sun included in Dates. So here displayed gallery is from 'Training request List' and we have Sat,Sun(Days) in Dates(SPList), I have tried with the below formula.

[Nogueira1306]

Hey! The problem is in your formula. I think the best solutions is this:

If( ThisItem.Days =  "Sat" Or ThisItem.Days = "Sun"; Color.Blue; Color.Red)

And, maybe, just paiting a collumn might be easier

If this solved your problem, please mark it as a solution 🙂 

[haris00000001]

How to calculate workdays with PowerApps

In some use cases you have to calculate the number of working days between a start date and an end date.

Because of there is no proper function available, you have to do calculate it on your own.

My sample App consists of

two date pickers for selecting the date range (datFrom and datTo)
a button to start the calculation
a label to display the result
My algorithm is like that:

Number of days

Calculate the number of days between the start date and the end date. Then add 1 to include the start date.

//Get Date Difference
UpdateContext(
  {
    ctxNumberOfDays: DateDiff(
      datFrom.SelectedDate;
      datTo.SelectedDate
    )+1
  }
);;
Weekends

Now calculate the number of days on a weekend.

Determine the number of weeks:

//Calculate Number of Weeks
UpdateContext(
 {
   ctxNumberOfWeeks: RoundDown((ctxNumberOfDays / 7);0)
 }
);;
Calculate the rest because 10 Days are one week and 3 Days:

//Calculate Days on top of full weeks: 10 Days: 1 Week, 3 Days Rest
UpdateContext(
 {
   ctxDaysRest: Mod(ctxNumberOfDays;7)
 }
);;
If we have one weeks, we can assume that we have 1 weekend.

But if the Weekday of datStart + 3 days (example above) are greater than the greates Weekday+1 (Saturday=7, The following Sunday would be 8 so everything greater than 8 means that we have walked over a Monday), we have to increase the number of weekends by 1.

More to Dates and Times: https://docs.microsoft.com/de-de/powerapps/maker/canvas-apps/functions/function-datetime-parts

Important:

This only works with Sunday as the first day of week (Default in Powerapps!) and Monday as the first workingday.

//Check if the Rest is on a weekend. If so, then we have one more weekend
If(
  Weekday(datFrom.SelectedDate) + ctxDaysRest > 8;
  UpdateContext({ctxWeekends: ctxNumberOfWeeks + 1});
  UpdateContext({ctxWeekends: ctxNumberOfWeeks})
);;
Now we have to calculate the number of weekend days. Because of one weekend are two days, we multiplicate the number of weekends by 2:

1
2
//Calculate Weekend days
UpdateContext({ctxWeekendDays: ctxWeekends * 2});;
Important:

In a real world scenario you have to prevent the user from selecting a weekend day and show a message like “You may not select a week end day”.

Public Holidays

Last but not least we have to check if there are any public holidays. In this sample I have created a simple collection of dates. You are free to put this data into a SharePoint list and filter them by the current year.

With CountIf I’m able to count how many entries of my holiday collection fits the condition to be greater or equal than the start date and less or equal than the end date:

ClearCollect(PublicHolidays;{Date:Date(2019;10;18)});;
UpdateContext(
 {
   ctxNumberOfPublicHolidays:
   CountIf(
     PublicHolidays;
     Date>=datFrom.SelectedDate;
     Date<=datTo.SelectedDate
     )
 }
);;
Final

To get the final result, you have to Take the complete number days – weekend days – public holidays:

//Calculate result
UpdateContext(
 {
   ctxWorkingDays: ctxNumberOfDays - ctxWeekendDays - ctxNumberOfPublicHolidays}
 );;
That’s it!

I hope you will find this useful.

[haris00000001]

please use the following code this will help you don't very.

[sindhureddy]

Hi,

Thanks for the reply. But my problem is I am displaying the records from one SPList(Training request List) but I have Days column in another SPList(Dates). Now I want to check Days in Dates(SPList) based on reqID(which is common in both the SPList) if any reqID has Sat,Sun in days column then i want to color that row in gallery.
This is my Dates SPListThis Training request List is displayed in the gallery

[timl · Accepted solution]

Hi @sindhureddy 

For your TemplateFill property, I would try to LookUp the date record like so:

With({dateRecord:LookUp(Dates, reqID=ThisItem.reqID)},
     If(dateRecord.Days ="Sat" Or dateRecord.Days = "Sun", Color.Red)
)

[sindhureddy]

Hi,
 I tried with your formula but it's not applying any color.

[timl · Accepted solution]

@sindhureddy 

To diagnose this, I would add a label into your gallery template and I would set the formula to this.

LookUp(Dates, reqID=ThisItem.reqID).Days

 Does the label correctly return the day name for each record?

[sindhureddy]

@timl 
I have tried with your formula but it's only displaying single day in label as we are using Lookup here. I have so many days for single reqID as shown below.so I have used filter to get all the days in label but it's showing error. Can you please look into this.

[haris00000001]

Please use this code Text(First(Filter(Dates,reqID)).Days)