Automate Working Day Calculations in Power Apps (No Excel Required)

Every Power Apps maker eventually runs into the challenge of calculating the exact number of working days between two dates. While Excel has the NETWORKDAYS function, Power Apps requires a different approach. By generating a complete list of dates and filtering out weekends and holidays, you can build a reliable business-day calculator that is easy to audit and maintain.

In this guide we will build a Freelancer Invoice Manager. A freelancer enters a contract’s start and end date, and the app automatically computes billable days — excluding Saturdays, Sundays, and a custom set of company holidays.

Data Preparation: Two SharePoint Lists

For this solution we need two SharePoint lists. Storing contract data and holidays in SharePoint gives business users the ability to update working days without entering Power Apps Studio.

ClientContracts

PublicHolidays

Building the Canvas App

Open Power Apps Studio and create a blank canvas app. Connect the ClientContracts and PublicHolidays lists as data sources.

Set the screen’s Fill property to a light gray so the form card stands out:

RGBA(245, 245, 245, 1)

Insert an Edit Form and bind it to the ClientContracts data source. Place the form cards in this order:

1. ContractTitle
2. ClientName
3. StartDate
4. EndDate
5. BillableDays

Set the DisplayMode of the BillableDays card to DisplayMode.View — the value must be automatically calculated, never typed in manually.

The Core Business Days Formula

The key idea is to materialise every single date between the start and end into a temporary table, then count only the rows that are weekdays and not in the holiday list.

Write the following formula into the Default property of the data card value inside the BillableDays card:

With(
  {
      varGeneratedDates: ForAll(
          Sequence(DateDiff(dp_EndDate.SelectedDate, dp_StartDate.SelectedDate, TimeUnit.Days) + 1),
          dp_StartDate.SelectedDate + (Value - 1)
      )
  },
  If(
      IsBlank(dp_StartDate.SelectedDate) Or IsBlank(dp_EndDate.SelectedDate),
      0,
      CountIf(
          varGeneratedDates,
          And(
              Weekday(Value) in [2, 3, 4, 5, 6],
              Not(Value in 'PublicHolidays'.HolidayDate)
          )
      )
  )
)

Let’s break down what this formula does:

1. DateDiff(… ) + 1 – calculates the total number of days in the range. The +1 ensures the start day is counted (a contract from Monday to Tuesday gives 2 days).
2. Sequence – creates a single-column table of numbers from 1 up to that total.
3. ForAll – converts each number into an actual date by adding the offset to the start date.
4. Weekday(Value) in [2,3,4,5,6] – keeps only Monday through Friday in the default system where Sunday = 1. If your environment uses a Monday-start week, adjust the list to [1,2,3,4,5].
5. Not(Value in 'PublicHolidays'.HolidayDate) – removes any date that appears in the holiday calendar.
6. CountIf – returns the number of dates that pass all filters.

Validating User Input

It is frustrating to select a weekend or holiday as a start or end date. You can provide real-time visual feedback by colouring the date picker borders.

StartDate BorderColor

If(
  And(
      Or(
          Weekday(Self.SelectedDate) in [1, 7],
          Self.SelectedDate in 'PublicHolidays'.HolidayDate,
          Self.SelectedDate > dp_EndDate.SelectedDate
      ),
      !IsBlank(Self.SelectedDate),
      !IsBlank(dp_EndDate.SelectedDate)
  ),
  Color.Red,
  Parent.BorderColor
)

EndDate BorderColor

If(
  And(
      Or(
          Weekday(Self.SelectedDate) in [1, 7],
          Self.SelectedDate in 'PublicHolidays'.HolidayDate,
          Self.SelectedDate < dp_StartDate.SelectedDate
      ),
      !IsBlank(Self.SelectedDate),
      !IsBlank(dp_StartDate.SelectedDate)
  ),
  Color.Red,
  Parent.BorderColor
)

Performance and Delegation Considerations

The CountIf that iterates over varGeneratedDates runs entirely in the browser against a local table. This means that step is never affected by delegation limits.

Common Mistakes

• Off-by-one errors – forgetting the +1 in DateDiff( . . . ) + 1. Without it the start date is excluded from the range.
• Wrong weekday numbering – default Weekday returns 1 for Sunday. Check your app’s Settings → Display → Start of week and adjust your in list accordingly.
• Blank date pickers – the IsBlank guard prevents the formula from erroring when no dates are selected.
• Hardcoding holidays – putting dates directly in the formula makes maintenance a nightmare. Always externalise them to a data source.

Extending the Pattern

The same Sequence + CountIf technique can be adapted for:

• Working hours – generate hours instead of days and filter out lunch breaks or after-hours times.
• Partial-day billing – combine it with Time columns to calculate half-day rates.
• Multi-region compliance – use multiple holiday lists and switch between them with a dropdown.

Final Recommendation

Using Sequence to create a materialised date range gives you full control over what is counted as a working day. It is readable, testable, and easy to modify. Keep your holiday data in a SharePoint list so administrators can manage it independently, and always add validation feedback to prevent incorrect entries. This pattern will serve you well in leave management, invoicing, project tracking, and any other business-day calculation scenario.

References

• Original concept by Matthew Devaney: Power Apps Calculate Business Days Excluding Weekends & Holidays
• Microsoft Learn: CountIf function in Power Apps
• Microsoft Learn: Sequence function in Power Apps
• Microsoft Learn: Weekday function in Power Apps