cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Hemasai1999
Helper III
Helper III

Identify weekends in between start time and end time and fill those row with red

Hi,

I have one gallery where I start date and end date I have to check whether user have selected weekends , If I have selected weekends then that particular row in that gallery should fill with red color. 

Screenshot (91).png

2 ACCEPTED SOLUTIONS

Accepted Solutions

Hi @Hemasai1999,

I just give you a sample, to convert the data type, you could check our official doc.

In addition, I don't think it is a good idea to separate the date and time into 2 columns, please consider combine them into a Date and Time column.

If(
    ThisItem.StartDate <ThisItem. EndDate && 1 + ((DateDiff(
        ThisItem.StartDate,
        ThisItem.EndDate,
        Days
    )) * 5 - ((Weekday(ThisItem.StartDate) - Weekday(ThisItem.EndDate))*2)) / 7 - Switch(
        Weekday(ThisItem.EndDate),
        7,
        1,
        0
    ) - Switch(
        Weekday(ThisItem.StartDate),
        1,
        1,
        0
    ) < DateDiff(
        ThisItem.StartDate,
        ThisItem.EndDate,
        Days
    ) + 1,
    Red
)
Best Regards,
Qi

View solution in original post

haris00000001
Advocate I
Advocate I

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.

 

View solution in original post

7 REPLIES 7
v-qiaqi-msft
Community Support
Community Support

Hi @Hemasai1999,

Do you want to validate if the date range including a weekend within?

Could you please share a bit more about the scenario?

Please set the TemplateFill property of the Gallery as below:

If(
    StartDate.SelectedDate < EndDate && 1 + ((DateDiff(
        StartDate.SelectedDate,
        EndDate.SelectedDate,
        Days
    )) * 5 - ((Weekday(StartDate.SelectedDate) - Weekday(EndDate.SelectedDate))*2)) / 7 - Switch(
        Weekday(EndDate.SelectedDate),
        7,
        1,
        0
    ) - Switch(
        Weekday(StartDate.SelectedDate),
        1,
        1,
        0
    ) < DateDiff(
        StartDate.SelectedDate,
        EndDate.SelectedDate,
        Days
    ) + 1,
    Red
)
Best Regards,
Qi

Hi,

Thanks for reply , but I already have days column in my SPLIst where I have stored (Mon,tue........)

based on that column and for that  particular reqID, if it is weekend then row in gallery should become red ,

 

This is my SPListThis is my SPList

 

Hi @Hemasai1999,

I just give you a sample, to convert the data type, you could check our official doc.

In addition, I don't think it is a good idea to separate the date and time into 2 columns, please consider combine them into a Date and Time column.

If(
    ThisItem.StartDate <ThisItem. EndDate && 1 + ((DateDiff(
        ThisItem.StartDate,
        ThisItem.EndDate,
        Days
    )) * 5 - ((Weekday(ThisItem.StartDate) - Weekday(ThisItem.EndDate))*2)) / 7 - Switch(
        Weekday(ThisItem.EndDate),
        7,
        1,
        0
    ) - Switch(
        Weekday(ThisItem.StartDate),
        1,
        1,
        0
    ) < DateDiff(
        ThisItem.StartDate,
        ThisItem.EndDate,
        Days
    ) + 1,
    Red
)
Best Regards,
Qi

View solution in original post

haris00000001
Advocate I
Advocate I

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.

 

View solution in original post

Thanks for reply will definitely try this

I tried this solution for myself and it worked.

thanks.

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

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