Automated flow to send reminder after specific Business Days (Excluding Weekends)

Prachi Sarkar
Jan 07
740
0
1

Article

In real-world business processes, reminders and follow-ups are usually calculated based on working days, not calendar days. If weekends are not excluded, reminders may be triggered too early or at incorrect times.

This article describes the implementation of an automated reminder mechanism using Power Automate and SharePoint Online. The solution calculates the required business days by excluding weekends, retrieves records from a SharePoint list based on past dates, and sends reminder notifications to users.

Step 1: Creating a Scheduled Flow in Power Automate

To begin, navigate to Power Automate Online by visiting: https://make.powerautomate.com/

From the left menu:

Select Create
Choose Scheduled cloud flow.

Configure the recurrence to run once per day

Step 2: Generate a Date Range for Business Day Calculation

Since Power Automate does not have a native “add business days” function, we generate a range of dates.

Add a Select action and name it Dates :

The expressions used in the select is as follows :

From: @{range(0,365)}
Map : Date : formatDateTime(subtractFromTime(utcNow(), item(), 'Day', 'yyyy-MM-dd'), 'yyyy-MM-dd')
Map : Day  : dayOfWeek(subtractFromTime(utcNow(), item(), 'Day'))

The output for select is an array of dates, and the day of the week. (ordered backward from today storing past 365 days)

Step 3: Excluding Weekends Using Filter Array

Next, add a Filter Array step, to reduce the dates array so that it only includes week days:

From : @body('Dates') // Output of Select action

Condition Expression

@and(not(equals(item()['Day'], 0)), not(equals(item()['Day'], 6)))

It ensures that:

Saturday (Day = 6) and Sunday (Day = 0) are excluded from the generated array list considering only the week dates and days.
The resulting output represents valid business days ordered backward from today.

Step 4: Using Compose to Pass a Specific Past Date to SharePoint (Optional but Recommended)

As we don’t want all past dates - we want to target a specific past working day, such as: 5 working days ago, 10 working days ago, N working days ago.

To achieve this cleanly, we use a Compose action and add an expression as follows:

@{formatDateTime(body('Filter_array')[5]['date'],'yyyy-MM-ddT00:00:00Z')}

You can change this value in [ ] :

[3] → 3 working days ago
[5] → 5 working days ago
[10] → 10 working days ago

This gives you full control over how far back you want to fetch records.

Step 5: Fetching SharePoint Records using Compose Output

Once the business date is identified the flow retrieves records from the SharePoint list using the Get items action.

The expression for filter query of Get Items is :

Status eq 'Report Generated' and ReferenceDate eq '@{outputs('Compose_-_Reference_Date')}'

The filter conditions ensure:

Only those records where Status (choice type column ) is 'Report Generated' and Reference Date (Date column) is matching past date are fetched.
Previously processed records are excluded

This keeps the automation efficient and prevents duplicate notifications.

Step 6: Sending Reminder Notifications (Final Step)

Since Get items can return multiple records, an Apply to each loop is required to process every matching SharePoint item individually.

Action Step : Apply to each
Expression for : Select as output from previous step :

@{outputs('Get_items')?['body/value']}

Action Step: Send an email (V2) : (Placed inside the Apply to each loop) :
The email action sends a reminder to the intended recipient using dynamic values from the SharePoint item.