One of the most common challenges in Power Automate is sending notifications based on business days. Since the platform does not have a built-in function to skip weekends or holidays, we often end up with calendar-based logic that sends a “3-day reminder” on a Monday for a task due on Wednesday.
The most reliable way to handle this is to run a daily flow that “walks” through the calendar day by day to calculate a true business-day gap.
How it works
Instead of pre-calculating dates, this flow runs every day, checks each active task, and calculates how many business days exist between today and the due date. If that count matches 1, 3, or 7, the email is triggered.
Step-by-step build guide
1. Global setup (top of flow)
Start with a Recurrence trigger set to run every 1 day. Before entering any loops, initialize your variables at the top level:
- varCount (Integer) – to store the business day count
- varToday (String) – current date without time
- varDate (String) – working date used in the loop
- varDueDate (String) – due date of the task
- varDirection (Integer) – controls forward or backward movement
- varIsBefore (Boolean) – indicates if task is upcoming or overdue
2. Get your tasks
Add a Get items action from SharePoint. Use a filter query such as: Status eq 'Active'. This ensures you only process relevant tasks.
3. Apply to each (process each task)
Inside the loop, reset variables for the current item:
- Set varCount = 0
- Set varToday = startOfDay(utcNow())
- Set varDate = variables('varToday')
- Set varDueDate = startOfDay(item()?['DueDate'])
- Set varDirection = if(less(variables('varToday'), variables('varDueDate')), 1, -1)
- Set varIsBefore = less(variables('varToday'), variables('varDueDate'))
4. Business day calculation (Do Until loop)
Add a Do Until loop with the condition:
equals(variables('varDate'), variables('varDueDate')) OR greaterOrEquals(variables('varCount'), 30)
Inside the loop:
Step A: Move date: Set varDate = addDays(variables('varDate'), variables('varDirection'))
Step B: Check if working day: Use a condition to check dayOfWeek(variables('varDate')). If the day is not 0 (Sunday) and not 6 (Saturday), then Increment varCount by 1.
5. Triggering the email
Now apply your conditions.
Before due date: Send email when varIsBefore is true AND varCount is 1, 3, or 7.
After due date: Send email when varIsBefore is false AND varCount is 1, 3, or 7.
Optional improvement (recommended)
To prevent duplicate emails during retries or re-runs, add a SharePoint column (e.g., LastReminderKey). Before sending an email, check if the key (like BEFORE_3) already matches. After sending, update the column.
Why this approach works well
Accuracy: It correctly treats a Friday-to-Monday gap as 1 business day, not 3 calendar days.
Flexibility: You can easily extend this to include holidays by checking against a holiday list.
Simplicity: No need for complex wait actions. The flow simply asks: “Is today a milestone day for this task?” and acts accordingly.
Let me know if it works!
✅ If this answer helped resolve your issue, please mark it as Accepted so it can help others with the same problem.
👍 Feel free to Like the post if you found it useful.
