Calculate Business Days Excluding Weekends in Power Apps

Question

In Power Apps, I have a form that includes a start date and an end date, and I need to calculate the number of days between them, excluding non-working days where the workweek starts on Sunday and the weekend consists of Friday and Saturday.

I tried the below fromula , but it include the weekend days

DateDiff(StartDate.SelectedDate, EndDate.SelectedDate)

I also asked ChatGPT, I got this formula that is not worked 

CountRows(
    Filter(
        AddColumns(
            Sequence(
                DateDiff(DatePickerStart.SelectedDate, DatePickerEnd.SelectedDate) + 1,
                0,
                1
            ),
            "CheckDate",
            DateAdd(DatePickerStart.SelectedDate, Value, Days)
        ),
        !Weekday(CheckDate, StartOfWeek.Monday) in [6, 7]
    )
)

Any help would be appreciated.

Answer 1

How to Calculate Business Days Excluding Weekends in Power Apps?

In Power Apps, to calculate the Business days excluding the Weekends and your week start with Sunday, you have to use the below formula

Set(
    Duration,
    With(
        {
            varDateRange: ForAll(
                Sequence(
                    DataCardValue30.SelectedDate - DataCardValue29.SelectedDate + 1
                ),
                DataCardValue29.SelectedDate + Value - 1
            )
        },
        If(
            And(
                IsBlank(DataCardValue29.SelectedDate),
                IsBlank(DataCardValue30.SelectedDate)
            ),
            0, // Return 0 if either date is blank
            CountIf(
                varDateRange,
                Weekday(Value, StartOfWeek.Sunday) in [1, 2, 3, 4, 5] // Sunday = 1, Friday = 6, Saturday = 7
            )
        )
    )
)

---

Breakdown of the Formula:

This sets a variable called Duration to store the final result.

Set(Duration,

This creates a temporary variable varDateRange that holds a table of all the dates from the start date to the end date.

With(
    {
        varDateRange: ForAll(
            Sequence(DataCardValue30.SelectedDate - DataCardValue29.SelectedDate + 1),
            DataCardValue29.SelectedDate + Value - 1
        )
    },

If either start or end date is blank, return 0 as the duration. (No valid input = no calculation.)

If(
    And(
        IsBlank(DataCardValue29.SelectedDate),
        IsBlank(DataCardValue30.SelectedDate)
    ),
    0,

This part filters the dates to only count working days.

CountIf(
        varDateRange,
        Weekday(Value, StartOfWeek.Sunday) in [1, 2, 3, 4, 5]
    )

And the Weekday(Value, StartOfWeek.Sunday) returns the day number (1–7) with Sunday as the first day of the week:

• Sunday = 1
• Monday = 2
• Tuesday = 3
• Wednesday = 4
• Thursday = 5
• Friday = 6
• Saturday = 7

So, this includes only days 1 to 5, from Sunday through Thursday = working days in most Gulf countries.

Hope it helps!

See Also PowerApps Business Days Between Two Dates