Power Automate: Calculate Working Days

Key insights

• Ian Tweedie presents a video demo of a Microsoft-certified Calculate Working Day connector for Power Automate.
  He shows how the connector handles weekends, custom work patterns and public holidays so flows produce correct business dates.
• The connector computes common date needs: next working day, previous working day, adding or subtracting a number of working days, and calculations that exclude configured holidays.
  It accepts custom working-week patterns so non-standard schedules work too.
• Practical uses include calculating SLAs, setting follow-up flags via Microsoft Graph, and determining billable days for invoices and timesheets.
  It fits approval flows, notifications, case management and project deadlines.
• Key advantages over manual formulas: it needs less custom logic (fewer loops and date expressions), handles regional or rotating schedules, and avoids placing due dates on weekends or holidays.
  This reduces flow complexity and errors.
• Typical setup steps: create a Power Automate connection, add the Calculate Working Day action, provide the start date, set days to add or remove, define the working-day pattern, and list holidays if needed.
  Then use the returned date in downstream actions like notifications or updates.
• Availability and fallback: the connector is a Premium connector, so it may not be available in some government or specialized clouds.
  If you cannot use it, apply a manual approach that loops day-by-day, checks weekday and holiday status, and counts valid working days.

Intro: Video summary and context

The YouTube demo, published by Microsoft, showcases a new connector called Calculate Working Day for Power Automate, presented by Ian Tweedie during the Power Platform monthly call on December 17. The short session walks viewers through practical examples of handling date logic including skipping weekends, applying custom work patterns, and excluding public holidays. In addition, the demo links the connector to real scenarios such as email SLAs, follow-up flags via Microsoft Graph, and calculating billable working days. Therefore, the video offers both a conceptual overview and hands-on examples useful for makers and IT teams.

What the demo demonstrates

First, the presenter explains how the connector determines the next or previous valid working date when given a start date and a pattern of working days. Then, he shows how to add or subtract a specified number of working days while honoring a configured holiday list, which avoids manually iterating days in a flow. Moreover, the demo highlights support for non-standard schedules, so organizations with weekend shifts or rotating rosters can set custom day codes. Consequently, viewers see how this offloads common date calculations that usually require complex expressions and loops.

How the connector works in Power Automate

The Calculate Working Day action sits in Power Automate as a premium connector and accepts inputs like start date, days to add or subtract, working-day pattern, and holiday dates. For example, you can define Monday-to-Friday as the default pattern using numeric codes or provide a bespoke sequence to match your business calendar. In addition, the action returns a computed date that you can pass to approvals, notifications, or downstream business logic without additional date arithmetic. As a result, flows become simpler and easier to maintain when this centralized calculation replaces manual loops and repeated expressions.

Advantages and tradeoffs

On the positive side, the connector reduces development time and lowers the chance of edge-case errors that arise from handcrafted expressions, particularly around holidays and cross-weekend calculations. However, there are tradeoffs to consider: the connector is premium, so teams must balance the cost against the maintenance savings and accuracy gains. Moreover, availability varies by cloud region and environment, and some government or sovereign clouds may not include the connector, which forces teams in those environments to rely on expression-based solutions. Therefore, decision-makers should evaluate licensing, regional support, and ongoing governance when planning adoption.

Challenges and alternative approaches

When the connector is unavailable or a team chooses not to use premium services, building the logic directly in a flow remains a practical option but comes with complexity. Traditionally, makers implement a loop that increments a date, checks the day of week, consults a holiday list, and only then increments a counter for valid working days, which increases flow complexity and testing effort. Additionally, maintaining holiday calendars across systems can be challenging and can introduce inconsistency if not centralized, so teams must weigh the cost of custom maintenance versus the convenience of a managed connector. Thus, the alternative approach gives full control but requires careful design and ongoing upkeep.

Implementation examples and best practices

The video shows practical use cases such as computing SLA deadlines for customer emails, setting follow-up flags via Microsoft Graph, and calculating billable days for invoicing, which illustrates how flows can be simplified by integrating the connector. For best results, the presenter recommends storing holiday lists centrally and testing with edge dates, and teams should also document working-day patterns for clarity across projects. Furthermore, architects should plan error handling for connector failures and fallbacks in regions where it is unavailable, thereby preserving reliability. Consequently, aligning technical planning with business rules improves predictability and reduces rework.

Conclusion and next steps for teams

In summary, the YouTube demo by Microsoft introduces a focused tool that streamlines common date calculations in Power Automate while supporting varied working schedules and holiday handling. While the connector simplifies flows and reduces manual logic, teams must consider licensing, regional availability, and the operational cost of centralizing holiday data. Therefore, organizations should pilot the connector in a non-critical process, compare it to a handcrafted flow, and measure time-to-implement, error rates, and total cost of ownership. Finally, this approach helps teams choose a path that balances accuracy, control, and ongoing maintenance.

Keywords

Calculate working days Power Automate, Power Automate business days calculation, Exclude weekends Power Automate, Power Automate calculate business days between dates, Add business days Power Automate, Power Automate working days formula, Power Automate networkdays function, Power Automate holidays and working days