Calculate Weekends and Holidays

[sdedic]

Hi all,

In my Power Apps app I have two formulas: one for calculating business days and another for weekends/holidays between two dates. While formula for calculating business days works perfectly fine and provides accurate results, the formula for weekends/holidays is somehow "broken", though they follow the same logic.

The problem with weekends/holidays formula is that it stops calculating weekends immediately after the first weekend is being calculated.

I was following @mdevaney  tutorial from here: https://www.matthewdevaney.com/power-apps-calculate-business-days-excluding-weekends-holidays/ and only slightly customized the formula.

So, here is the formula for calculating business days added to the button OnSelect property:

With(
    {
    // generate a one-column table of all dates between start date & end date
        varDateRange: ForAll(
            Sequence(DateValue(DefaultTerminationDate1.Text) - LastDayOnProject.SelectedDate),
            LastDayOnProject.SelectedDate + Value
        )
    },
        // show only dates Monday to Friday and exclude holidays
        CountIf(
            varDateRange,
            And(
                Weekday(Value) in [2, 3, 4, 5, 6],
                Not(Value in 'Public Holidays'.Date)
        )
    )
)

And the result is as expected:

Here is the formula for calculating weekends and holidays between two dates:

With(
    {
     // generate a one-column table of all dates between start date & end date
        varDateRange: ForAll(
            Sequence(ContractTerminationDate1.SelectedDate - LastDayOnProject.SelectedDate),
            LastDayOnProject.SelectedDate + Value
        )
    },
        // show weekends and holidays
    CountIf(
        varDateRange,
        Or(
            Weekday(Value) in [1, 7],
            Value in 'Public Holidays'.Date
        )
    )
)

But when I click the button to calculate these days the formula calculates only first weekend and then stops:

If I click the button again to calculate it adds one more day and I have to repeat the process for that many times there are weekends/holidays between two dates used in calculation:

I need the app to calculate weekends/holidays automatically, so the option of clicking the button until getting the final accurate result is not an option. So, the above was made just to be able to explain the problem.

Does anyone know why is this happening and what I am missing in the formula for calculating weekends/holidays? 

Any help or advice is more than appreciated.

Thank you.

Best

[sdedic]

I've tried excluding holidays table from the formula to see if that creates the problem, I've also tried different combinations of AND and OR operators, but nothing helped and the formula still doesn't work as it should. It still stops calculating after the first weekend.

[sdedic]

I think I got it. It looks like the formula works perfectly fine except in one case. Since this formula calculates weekends/holidays between 'Last day on project' and 'Contract termination date' and 'Contract termination date' is also a calculated date, there is one case where calculation of 'Contract termination date' requires weekends/holidays in order to get the final date. When that case happens, the calculation of weekends/holidays can't be accurate as we still don't have final 'Contract termination date' and the calculation of weekends/holidays is based on inaccurate 'Contract termination date'.

Though I didn't find the solution for this, at least I figured out what's the problem! 😀