Power Platform Community Forum Thread Details

Hello everyone,

I want calculate days between two dates in canvas app.

also want to excluding weekends and holidays but including last Saturday of the month.

i write below code for excluding weekends and including last Saturday of the month, i want to add logic of holiday list

can anyone please suggest me how to achieve this?

If(
 StartDatePicker.SelectedDate = EndDatePicker.SelectedDate,
 Text(
 If(
 [@DD_Half_Full_day].SelectedText.Value = "Half Day",
 "0.5",
 1 + ((DateDiff(
 StartDatePicker.SelectedDate,
 EndDatePicker.SelectedDate,
 Days
 )) * 5 - ((Weekday(StartDatePicker.SelectedDate) - Weekday(EndDatePicker.SelectedDate))*2)) / 7 - Switch(
 Weekday(EndDatePicker.SelectedDate),
 7,
 1,
 0
 ) - Switch(
 Weekday(StartDatePicker.SelectedDate),
 1,
 1,
 0
 ) + If(
 (With(
 {
 NextMonth: DateAdd(
 Date(
 Year(Now()),
 Month(Now()),
 1
 ),
 1,
 Months
 )
 },
 DateAdd(
 NextMonth,
 -1 * Weekday(
 DateAdd(
 NextMonth,
 -7
 )
 )
 )
 ) > StartDatePicker.SelectedDate - 1) && (With(
 {
 NextMonth: DateAdd(
 Date(
 Year(Now()),
 Month(Now()),
 1
 ),
 1,
 Months
 )
 },
 DateAdd(
 NextMonth,
 -1 * Weekday(
 DateAdd(
 NextMonth,
 -7
 )
 )
 )
 ) < EndDatePicker.SelectedDate + 1),
 1,
 0
 )
 )
 ),
 StartDatePicker.SelectedDate > EndDatePicker.SelectedDate,
 "0",
 Text(
 1 + ((DateDiff(
 StartDatePicker.SelectedDate,
 EndDatePicker.SelectedDate,
 Days
 )) * 5 - ((Weekday(StartDatePicker.SelectedDate) - Weekday(EndDatePicker.SelectedDate))*2)) / 7 - Switch(
 Weekday(EndDatePicker.SelectedDate),
 7,
 1,
 0
 ) - Switch(
 Weekday(EndDatePicker.SelectedDate),
 1,
 1,
 0
 ) + If(
 (With(
 {
 NextMonth: DateAdd(
 Date(
 Year(Now()),
 Month(Now()),
 1
 ),
 1,
 Months
 )
 },
 DateAdd(
 NextMonth,
 -1 * Weekday(
 DateAdd(
 NextMonth,
 -7
 )
 )
 )
 ) > EndDatePicker.SelectedDate - 1) && (With(
 {
 NextMonth: DateAdd(
 Date(
 Year(Now()),
 Month(Now()),
 1
 ),
 1,
 Months
 )
 },
 DateAdd(
 NextMonth,
 -1 * Weekday(
 DateAdd(
 NextMonth,
 -7
 )
 )
 )
 ) < EndDatePicker.SelectedDate + 1),
 1,
 0
 )
 )
)

also below is code for excluding holiday,

CountIf('Holiday List', Date >= StartDatePicker.SelectedDate, Date <= EndDatePicker.SelectedDate)

Thank you all in advance

Categories:

Building Power Apps

Hi everyone,

I've been working on a PowerApps solution to calculate the number of days between two dates, excluding weekends and holidays. I also want to include the last Saturday of each month in the calculation. I would appreciate some guidance or suggestions on how to achieve this.

Currently, I have implemented the logic to exclude weekends and holidays using the steps mentioned earlier in this thread. However, I'm unsure about how to incorporate the last Saturday of the month into the calculation.

Here's a summary of the steps I've taken so far:

Created a SharePoint list to store the holiday dates.
Used the DateTimeValue function to convert the date inputs into DateTime values.
Created a variable to store the total number of days.
Utilized a ForAll loop to iterate over each day between the start and end dates, incrementing the totalDays variable only if the day is not a weekend or holiday.

Now, I would like to enhance this solution by including the last Saturday of the month in the total number of days. Could you please provide some insights or suggestions on how to achieve this?

Thank you all for your help and support!

Best regards,
Raju Soneri