Automating Recurring Calendar-Based Reminders with Power Automate: Building Scheduled Flows That Check SharePoint Dates, Calculate Deadlines, and Send Personalized Outlook Notifications

Introduction

Picture this: your team maintains a SharePoint list of vendor contracts, each with a renewal date buried somewhere in the ContractExpiry column. Every quarter, someone manually scrolls through that list, squints at dates, and fires off reminder emails one by one. Half the time the reminders go out too late. Sometimes they don't go out at all. When a contract lapses because nobody caught it in time, the post-mortem conversation always ends with the same conclusion: "We need to automate this."

That scenario plays out across dozens of real business contexts — certification renewals, equipment inspections, software license expirations, employee performance review cycles, compliance deadlines. The common thread is that someone owns a list of date-stamped records, someone else needs to know when those dates are approaching, and the whole process is currently held together with sticky notes and good intentions. This lesson teaches you how to replace that fragile, manual process with a robust scheduled flow that runs itself every morning, checks your SharePoint list, calculates how many days remain until each deadline, and fires off personalized, context-rich Outlook emails to exactly the right people.

By the end of this lesson, you'll have a working flow you can deploy and adapt immediately. More importantly, you'll understand why each piece works the way it does — the date arithmetic, the filtering logic, the notification structure — so you can extend it to any recurring reminder scenario you encounter.

What you'll learn:

• How to structure a SharePoint list to support deadline tracking and scheduled automation
• How to build and configure a Recurrence trigger that runs on a meaningful schedule
• How to use addDays, utcNow, and date formatting expressions to calculate and compare deadlines
• How to filter SharePoint items server-side so your flow only processes records that actually need attention
• How to build personalized Outlook emails with dynamic content that gives recipients exactly the context they need to act

---

Prerequisites

You should already be comfortable with the Power Automate designer and have built at least one working flow. You should know what a connector action looks like and understand the difference between static and dynamic content. If you're still finding your footing with how flows are structured, spend a few minutes with Getting Started with the Power Automate Interface before continuing.

You'll also want a SharePoint site where you can create a list for this exercise. A Microsoft 365 account with access to Power Automate, SharePoint, and Outlook is required.

---

Designing the SharePoint List

Before you write a single flow action, you need a data source that's structured to support automation. This is the step most tutorials skip, and it's the reason those tutorials produce flows that fall apart under real conditions.

For this lesson, we'll use a Vendor Contracts list. Open your SharePoint site and create a new list with the following columns:

• Title (default text column) — the vendor or contract name
• ContractExpiry (Date and Time column, date only) — the actual expiration date
• OwnerEmail (Single line of text) — the email address of the person responsible for this contract
• OwnerName (Single line of text) — the owner's display name for personalized greetings
• ReminderSent (Yes/No column, default: No) — a flag to prevent duplicate reminders
• ContractValue (Currency) — optional, but useful for prioritizing urgent renewals
• Status (Choice: Active, Expired, Renewed) — so you only send reminders on active contracts

The ReminderSent column is one of those small design decisions that pays enormous dividends. Without it, your flow will send the same reminder every single day the contract remains within the notification window. That's annoying at best and potentially damaging to your credibility at worst. We'll use this column to mark records once the reminder has gone out, and reset it as part of a separate maintenance flow (or manually after renewal action is taken).

The OwnerEmail column deserves a word of explanation. You might be tempted to use a People column here instead of plain text. People columns are great for SharePoint's built-in features, but they introduce complexity in Power Automate because the value comes back as a JSON object, not a simple string. For this flow, a plain text email address keeps the logic clean. If your organization requires People columns, check out Automating SharePoint List Item Lifecycle Management with Power Automate for patterns that handle People column values correctly.

Add five or six sample records with a mix of expiration dates — some 60 days out, some 30 days, some 7 days, and one that's already past. Set Status to "Active" on all of them and ReminderSent to "No."

---

Understanding the Recurrence Trigger

A scheduled flow doesn't wait for something to happen — it wakes up on a timer and goes looking for work. The Recurrence trigger is what makes this possible.

Create a new Scheduled cloud flow in Power Automate. Name it something descriptive: Contract Expiry - Daily Reminder Check. When you choose the Scheduled flow type, Power Automate immediately prompts you to configure the recurrence.

For a daily reminder flow, set:

• Interval: 1
• Frequency: Day
• Start time: Set this to 07:00:00 AM in your organization's primary time zone
• Time zone: Your local time zone (not UTC)

That last setting — time zone — is more important than it looks. If you leave it blank, the trigger runs in UTC, which means your "7 AM" reminder might land in inboxes at 2 AM local time. Explicitly setting the time zone anchors the schedule to human working hours.

Once you've created the flow, click the Recurrence trigger to expand its advanced options. You'll see fields for On these days, which lets you restrict execution to weekdays. For contract reminders, running Monday through Friday is usually appropriate — you don't want to generate action items on Saturday morning.

---

The Core Date Calculation Logic

This is the intellectual heart of the flow, and it's where most practitioners get stuck. Let's work through the logic before we build it.

Your goal is to identify contracts expiring within the next 30 days. That means for each SharePoint record, you need to answer: "How many days are between today and this contract's expiry date?" If the answer is between 0 and 30, you send a reminder. If the answer is negative (already expired), you handle that separately. If it's greater than 30, you skip it.

Power Automate doesn't have a native "days between dates" function, but you can calculate it using ticks(). The ticks() function converts a datetime string into the number of 100-nanosecond intervals since January 1, 0001. When you subtract one ticks value from another and divide appropriately, you get the difference in days.

The formula for days between today and an expiry date looks like this:

div(sub(ticks(item()?['ContractExpiry']), ticks(utcNow())), 864000000000)

Let's break that down:

• ticks(item()?['ContractExpiry']) — converts the SharePoint date field to ticks
• ticks(utcNow()) — converts the current UTC timestamp to ticks
• sub(...) — subtracts today's ticks from the expiry ticks (positive = future, negative = past)
• 864000000000 — the number of 100-nanosecond intervals in one day (1 day × 24 hours × 60 min × 60 sec × 10,000,000)
• div(...) — integer division to get whole days

You'll use this expression inside a Condition or inside a Compose action to evaluate each record. We'll see exactly where in the next section.

For the date formatting in your emails, you'll want to convert the raw ISO timestamp into something human-readable. The expression below produces "September 14, 2025":

formatDateTime(item()?['ContractExpiry'], 'MMMM dd, yyyy')

If you want to go deeper on date expressions and other formula patterns, Mastering Dynamic Expressions and the Power Automate Formula Language covers the full toolkit.

---

Querying SharePoint Efficiently with Filter Query

A naive flow would use Get items to pull every record in the list and then loop through all of them with a condition inside the loop. For a 50-row list, that works fine. For a 5,000-row list, you'll hit pagination issues, run into the 5,000-item SharePoint query threshold, and waste connector calls processing records you're going to discard anyway.

The better approach is to filter at the server, asking SharePoint to return only the records that could possibly need a reminder. You do this with the Filter Query field in the Get items action.

Add a Get items action after the Recurrence trigger. Configure it as follows:

• Site Address: your SharePoint site
• List Name: Vendor Contracts

Now expand Advanced options and fill in the Filter Query:

Status eq 'Active' and ReminderSent eq 0

This tells SharePoint's OData API to only return Active contracts where the reminder hasn't been sent yet. We're not filtering by date here — that comparison is harder to express correctly in OData when you're dealing with calculated future dates — so we'll do the date math in the flow. But this filter alone eliminates a huge chunk of irrelevant records before they even leave SharePoint.

Also set Top Count to something reasonable — 500 is usually a safe upper limit for daily processing. If your list grows beyond that, you'll want to add pagination handling, which is a more advanced topic covered in the SharePoint list lifecycle management article linked above.

---

Looping Through Records and Calculating Days Remaining

With filtered records coming back from SharePoint, you now need to loop through them, calculate the days remaining for each, and branch based on the result.

Add an Apply to each action and connect it to the output of Get items (value). Inside the loop, add a Compose action to calculate days remaining. Name it Calculate Days Remaining. In the Inputs field, enter:

div(sub(ticks(items('Apply_to_each')?['ContractExpiry']), ticks(utcNow())), 864000000000)

Notice the change from item() to items('Apply_to_each') — when you're inside an Apply to each, you reference the current item using the loop's name. Power Automate generates this name automatically based on the action name you see in the designer. If your Apply to each has a different name, adjust accordingly.

After the Compose, add a Condition action. Configure it to check:

outputs('Calculate_Days_Remaining')

is greater than or equal to 0

AND

outputs('Calculate_Days_Remaining')

is less than or equal to 30

This creates a 30-day notification window. Contracts expiring in 0–30 days will go into the Yes branch. Everything else (more than 30 days out, or already expired) goes to No and does nothing.

Inside the Yes branch, you'll build the notification logic. Inside the No branch, leave it empty for now — you'll add expired contract handling later if needed.

---

Building the Personalized Outlook Email

Now comes the part that makes the difference between a reminder people act on and one they ignore: personalization and context.

Inside the Yes branch of your condition, add a Send an email (V2) action from the Office 365 Outlook connector. Configure it as follows:

To: Use dynamic content to reference the OwnerEmail field from the current SharePoint item.

Subject: Build a dynamic subject line that includes the contract name and days remaining:

Action Required: [items('Apply_to_each')?['Title']] contract expires in [outputs('Calculate_Days_Remaining')] days

In the designer, you'll build this by mixing static text with dynamic content tokens. Type "Action Required: " then click "Add dynamic content," select Title, type " contract expires in," click dynamic content again and select the output of your Compose action, then type " days."

Body: This is where you make the email genuinely useful. Here's a body template to work from:

Hi [OwnerName],

This is an automated reminder that the following vendor contract is approaching its expiration date and requires your attention.

Contract Details:
- Vendor / Contract: [Title]
- Expiration Date: [formatDateTime(items('Apply_to_each')?['ContractExpiry'], 'MMMM dd, yyyy')]
- Days Remaining: [outputs('Calculate_Days_Remaining')]
- Contract Value: [ContractValue]

Please log in to the Vendor Contracts list to review this contract and update its status once renewal or termination action has been taken.

[Link to SharePoint List]

If you believe you have received this message in error, or if this contract has already been renewed, please update the Status field in SharePoint and set "Reminder Sent" to Yes to stop future notifications.

This is an automated message sent by the Contract Expiry Reminder Flow. Do not reply to this email.

To include the link to your SharePoint list, you can hardcode the URL as an HTML hyperlink if you switch the email body to HTML mode. Click the </> icon in the body field to toggle to HTML, then wrap your link like this:

<a href="https://yourcompany.sharepoint.com/sites/YourSite/Lists/VendorContracts">View Vendor Contracts List</a>

For contracts expiring in 7 days or fewer, you might want to also add the contract owner's manager to the CC field — escalating automatically without any extra manual steps. You can hardcode a manager's email or pull it dynamically from a Manager column you add to your SharePoint list.

For patterns connecting multiple Microsoft 365 services in a single flow, Chaining Microsoft 365 Connectors in a Single Flow is worth reading alongside this lesson.

---

Updating the ReminderSent Flag

Sending the email is only half the job. Immediately after the Send email action (still inside the Yes branch), add an Update item action from the SharePoint connector. Configure it to:

• Site Address: your SharePoint site
• List Name: Vendor Contracts
• Id: the ID field from the current loop item (items('Apply_to_each')?['ID'])
• ReminderSent: Yes (select the toggle for the Yes/No field)

This marks the record so tomorrow's flow run ignores it. Without this step, your flow will send the same person the same email every morning until they take action or the contract expires — which is exactly the kind of automation that makes people distrust automation.

There's a subtle architectural question here: what happens when a contract gets renewed? The ReminderSent flag will be "Yes," so no more reminders — which is correct. But if the renewal date is updated and Status is set back to "Active," you want reminders to start flowing again. The cleanest solution is a separate flow triggered by item modification: when ContractExpiry is updated on an Active record, reset ReminderSent to No. That trigger-based pattern is covered in Automating SharePoint List Item Lifecycle Management with Power Automate.

---

Handling Edge Cases: Expired Contracts and Multiple Notification Thresholds

A production-ready flow needs to handle more than the happy path. Let's look at two common extensions.

Expired Contract Notifications

Contracts where outputs('Calculate_Days_Remaining') is negative are already expired. You probably want different handling for these — maybe a more urgent notification to the owner and their manager, or a flag change to Status = "Expired."

Inside the No branch of your main condition, add another condition:

outputs('Calculate_Days_Remaining') is less than 0

In the Yes sub-branch of that nested condition, add a separate Send email action with subject "EXPIRED: [Title] contract requires immediate attention" and include your escalation recipients. Then add an Update item action to change Status to "Expired." This keeps your list accurate without anyone having to manually track it.

Multiple Notification Thresholds

If your business needs reminders at 60 days, 30 days, and 7 days, you'll want to rethink the ReminderSent column. A single boolean flag doesn't carry enough information — once the 60-day reminder goes out, the flag is set to Yes, and the 30-day and 7-day reminders never fire.

Replace the ReminderSent Yes/No column with a ReminderStage Choice column with values: None, Sent60Day, Sent30Day, Sent7Day. Then adjust your condition logic:

• If days remaining ≤ 60 AND days remaining > 30 AND ReminderStage = "None" → send 60-day reminder, set ReminderStage = "Sent60Day"
• If days remaining ≤ 30 AND days remaining > 7 AND ReminderStage ≠ "Sent30Day" → send 30-day reminder, set ReminderStage = "Sent30Day"
• If days remaining ≤ 7 AND ReminderStage ≠ "Sent7Day" → send 7-day reminder, set ReminderStage = "Sent7Day"

This approach is more complex but gives you genuine escalation behavior. For a fully worked escalation pattern including Teams notifications at the final stage, Automating Recurring Approval Reminders in Power Automate walks through the full design.

---

Hands-On Exercise

Build the complete Vendor Contracts reminder flow from scratch using the steps in this lesson. Once it's working for the basic 30-day case, extend it in the following ways:

Step 1 — Build the base flow: Set up your SharePoint list with the columns described above. Add at least six test records:

• Two contracts expiring in 45 days (Status: Active, ReminderSent: No)
• Two contracts expiring in 15 days (Status: Active, ReminderSent: No)
• One contract expiring in 3 days (Status: Active, ReminderSent: No)
• One contract already expired (Status: Active, ReminderSent: No)

Build the Recurrence trigger, Get items with filter, Apply to each, Compose for days remaining, Condition, Send email, and Update item actions exactly as described.

Step 2 — Test manually: Don't wait for tomorrow morning's scheduled run. Use the Test button in Power Automate and select "Manually" to trigger the flow immediately. Check which records it processes. Verify that only the three contracts within 30 days receive emails. Check that the 45-day contracts are skipped and the expired contract lands in the No branch.

Step 3 — Verify the flag: After testing, go back to your SharePoint list. The three records that received emails should now have ReminderSent = Yes. Run the flow again manually — this time, no emails should go out because all eligible records are already flagged.

Step 4 — Extend with urgency tiers: Add a nested condition inside the Yes branch that checks if days remaining is 7 or fewer. If true, add "URGENT:" to the subject line and add a hardcoded manager email to the CC field. If false, send the standard reminder. This teaches you to build branching logic inside an existing Yes branch.

Step 5 — Check your run history: Open the flow's run history and click through one of the successful runs. Expand each action and review its inputs and outputs. Find the Compose action for days remaining and verify the number it calculated. This habit of reading run history is how you catch subtle bugs before they become user complaints. The Using Power Automate Run History and Flow Checker article teaches you exactly what to look for.

---

Common Mistakes & Troubleshooting

Problem: The Get items filter query returns no results even though matching records exist.

This is almost always an internal name mismatch. Go to SharePoint List Settings, click on the column, and read the Field= parameter in the URL. Use that exact string in your OData filter. Also remember that Yes/No columns filter as eq 0 (No) and eq 1 (Yes), not eq 'No' or eq 'Yes'.

Problem: The days remaining calculation is wildly wrong — showing 18,250 instead of 30.

You forgot the div() wrapper. Without it, you're seeing raw ticks difference, which is a number in the hundreds of billions. Make sure your full expression is div(sub(ticks(...), ticks(utcNow())), 864000000000).

Problem: Days remaining is always showing as a negative number, even for future dates.

You have the subtraction backwards. The correct order is sub(ticks(ExpiryDate), ticks(utcNow())) — expiry minus today. If you wrote it as sub(ticks(utcNow()), ticks(ExpiryDate)), future dates will always be negative.

Problem: The email is going out but the To field shows "null" or the email doesn't arrive.

Check that your OwnerEmail column actually has values in the test records. SharePoint doesn't enforce non-null on text columns, so it's easy to have blank email addresses. Add a condition before the Send email action: check that the OwnerEmail field is not empty. If it is empty, skip the notification and log the record to a separate SharePoint list of "broken records" for human review.

Problem: The Update item action fails with a 400 error.

Almost always this is because you're passing the wrong ID value, or you're trying to set a field that doesn't accept the value you're sending. Expand the failed action in run history and read the error message body — it usually tells you exactly which field caused the problem. Double-check that you're passing the integer ID from items('Apply_to_each')?['ID'], not the Title or a formatted value.

Problem: The flow runs fine manually but seems to skip records when running on schedule.

Check your time zone settings on the Recurrence trigger. If the flow is running at midnight UTC and your dates are stored in a different offset, the utcNow() comparison might straddle a day boundary unexpectedly. Set an explicit start time of 8:00 AM in your local time zone to keep it well away from midnight boundaries.

---

Summary & Next Steps

You've built something real and immediately useful here. A scheduled flow that wakes up every morning, queries SharePoint for contracts approaching expiration, calculates days remaining with a ticks-based expression, filters intelligently to avoid duplicate notifications, sends personalized Outlook emails with actionable context, and marks records as processed — that's not a tutorial exercise, that's a production automation.

The pattern you've built is generalizable. Swap "vendor contracts" for "employee certifications," "software licenses," "insurance policies," or "equipment calibration due dates" and the structure stays exactly the same. The SharePoint list columns change, the email body changes, but the flow architecture — recurrence trigger → filtered get items → apply to each → calculate days → condition → notify → update flag — is a template you'll reuse across your organization.

Where to go from here:

The natural next extension of this flow is adding Teams channel notifications alongside the Outlook emails — posting a summary card to a "Contract Management" channel each morning that shows all contracts in the 30-day window at a glance. Using Power Automate with Microsoft Teams shows you how to connect that channel post without adding significant complexity to your existing flow.

If some of your contracts require formal sign-off before renewal, you can chain an approval action into the Yes branch immediately after sending the reminder email. When the owner clicks "Approve," the flow updates the Status to "Renewed" and resets the reminder flag automatically. Building Approval Workflows with Power Automate covers the approval connector patterns you'd need.

Finally, if you're going to manage a library of flows like this one — different deadline flows for different lists, different departments, different reminder windows — understanding how to organize and share them cleanly will save you significant headaches. Organizing and Reusing Logic with Power Automate Environments, Connections, and Flow Templates is the right next read.