Share - calculate working days between two given dates

[Li]

Hello, I was trying to find the method to return the number of working days between two given dates and found the following article

https://powerapps.microsoft.com/en-us/blog/excluding-weekends-and-holidays-in-date-differences-in-po...

However, as the author mentioned "Notice that the partial week calculation given above will not work if the start or end dates fall on weekends"

So I spent some time writing the following formula which seems work for weekends too:

If(IsNewRequestFormValid, RoundDown(DateDiff(DatePickerFrom.SelectedDate, DatePickerTo.SelectedDate, Days) / 7, 0) * 5 +
If(Weekday(DatePickerTo.SelectedDate) >= Weekday(DatePickerFrom.SelectedDate),
If(Weekday(DatePickerTo.SelectedDate) = Weekday(DatePickerFrom.SelectedDate) && (Weekday(DatePickerTo.SelectedDate) = 1 || Weekday(DatePickerTo.SelectedDate) = 7),
0,
If(RoundDown((Weekday(DatePickerTo.SelectedDate) - Weekday(DatePickerFrom.SelectedDate))/5,0) >= 1,
5,
If(Weekday(DatePickerTo.SelectedDate) = 7,
Weekday(DatePickerTo.SelectedDate) - Weekday(DatePickerFrom.SelectedDate),
If(Weekday(DatePickerFrom.SelectedDate) = 1,
Weekday(DatePickerTo.SelectedDate) - Weekday(DatePickerFrom.SelectedDate),
Mod(Weekday(DatePickerTo.SelectedDate) - Weekday(DatePickerFrom.SelectedDate), 5) + 1)))),
(7 - Weekday(DatePickerFrom.SelectedDate) + Weekday(DatePickerTo.SelectedDate) - 1)))

I am sure it can be improved, so please feel free to make any enhancement. 

[rwittels]

Hi Ipr. I just read this article and also thought about this. I would say that one could set a variable on the OnSelect property of the form's submit button which sets a true or false value for all fields being validated e.g.

Set( IsNewRequestFormValid, !IsBlank(Name) && !IsBlank(Department) && !IsBlank(ContactNumber) && !IsBlank(StartDate) && !IsBlank(EndDate) )

The IsNewRequestFormValid variable would set to true if all fields are not blank. Then using "If(IsNewRequestFormValid,True,False)" would then return the true value for the if statement.

[andypi]

If it is of any interest, I found this to become fairly readable and easy to flip to count only weekends:

CountIf(
    AddColumns(
        Sequence(
            1+DateDiff(
                StartDatePicker.SelectedDate,
                EndDatePicker.SelectedDate,
                Days
            )
            ,Weekday(StartDatePicker.SelectedDate, 3)
        ),
        "weekday",
        !RoundDown(Mod(Value,7)/5,0)
    ), 
    weekday
)

 Not sure about performance hits of this (CountIf), vs Sum vs above conditionals and direct calculations?