Lease Critical Dates: Track Renewal Option and Termination Notice Deadlines with Power Automate (Part 2)

In Part 1 we built a flow that reminds you when leases are 3, 6, 9, 12 or 18 months from expiry. That's useful, but the expiry date is rarely the date that costs a client money. The dangerous dates are the notice deadlines hidden in the lease.

A tenant with a renewal option usually has to exercise it in writing 6, 9 or 12 months before expiry. Miss that window by a day and the option is gone, and the landlord can re-lease the space or name a new rent. The same goes for early termination rights, expansion options and rent reviews. Every broker has a story about a client who found out too late.

In this guide we'll build a simple, no-AI Power Automate flow that reads a critical dates table in Excel, works out how many days are left before each notice deadline, and emails you one colour-coded digest every morning with everything due in the next 90 days, overdue items first.

What you'll need

• Microsoft 365 with Excel in OneDrive for Business (or SharePoint) and Outlook.

• Power Automate. Everything here uses standard connectors, so no premium licence is needed.

• Your lease abstracts, or at least the critical date clauses from each lease.

Step 1: Build the critical dates table in Excel

Create a workbook called Lease-Critical-Dates.xlsx (or add a sheet to your Part 1 tracker) with a table named CriticalDates and these columns:

• Lease ID and Tenant: to tie each right back to your lease tracker.

• Property: the address and unit.

• Right: Renewal option, Early termination, Expansion option or Rent review.

• Event Date: the date the right takes effect, for example the lease expiry date for a renewal option.

• Notice (Months): how many months before the event date the notice has to be delivered, straight from the lease.

• Notice Deadline: a formula, so you never calculate it by hand.

• Days Left: a formula for anyone looking at the sheet.

• Notice Sent: Yes or No. Set it to Yes once the notice has been delivered.

• Notes: the clause in plain English, like "One 5-year option at 95% of market rent".

The two formula columns are:

Notice Deadline:  =EDATE([@[Event Date]], -[@[Notice (Months)]])
Days Left:        =[@[Notice Deadline]] - TODAY()

EDATE counts calendar months and handles month ends correctly, so a June 30 expiry with 6 months' notice gives December 30, not December 31. Add conditional formatting on the table (red for 7 days or less, orange for 30, yellow for 90, skipping rows where Notice Sent is Yes) and the sheet becomes a dashboard on its own.

One lease can have several rows. Northwind Logistics in our sample has both a renewal option and an expansion option, each with its own notice period.

Step 2: Create the scheduled flow

In Power Automate, choose Create > Scheduled cloud flow, name it Lease Critical Dates Digest and set it to run every day at 7:00 AM. Open the Recurrence trigger and set the Time zone to your own (we use Eastern Time), so "today" means today where you work.

Add a Compose action named Today with this expression:

formatDateTime(convertFromUtc(utcNow(),'Eastern Standard Time'),'yyyy-MM-dd')

Here's the whole flow:

Step 3: Read the table

Add Excel Online (Business) > List rows present in a table and point it at your workbook and the CriticalDates table. Under Advanced parameters, set DateTime Format to ISO 8601. Without it, Excel dates come back as serial numbers like 46291 instead of 2026-09-26.

Step 4: Work out the days left

Don't rely on the Days Left column in Excel. TODAY() only recalculates when someone opens the workbook, so the flow calculates its own. Add a Data Operation > Select named Add days left. Map the columns you want in the email and add one extra key, DaysLeft:

div(sub(ticks(item()?['Notice Deadline']), ticks(outputs('Today'))), 864000000000)

ticks() returns the number of 100-nanosecond intervals since year 1, and there are 864,000,000,000 of those in a day, so the result is a whole number of days. It's negative when the deadline has already passed.

Step 5: Keep only the deadlines that matter

Add a Filter array named Open deadlines on the Select output, switch to advanced mode and use:

@and(not(equals(item()?['Notice Sent'],'Yes')), lessOrEquals(item()?['DaysLeft'],90))

That keeps everything in the next 90 days and everything overdue that nobody has dealt with yet. An overdue deadline stays on the digest every day until someone confirms the notice went out, which is exactly the nagging you want.

Add a second Filter array named Overdue on the output of Open deadlines:

@less(item()?['DaysLeft'],0)

Then add a Condition, Anything to report, that checks length(body('Open_deadlines')) is greater than 0. On quiet days, nobody gets an empty email.

Step 6: Build a colour-coded table

Create HTML table can't colour individual rows, so we build the rows ourselves. Inside the True branch, add a Select named Digest rows. Set From to the open deadlines sorted so the most urgent come first:

sort(body('Open_deadlines'),'DaysLeft')

Switch the Map to text mode and build one HTML row per deadline. The row colour depends on DaysLeft:

if(less(item()?['DaysLeft'],0),'#f8d7da',if(lessOrEquals(item()?['DaysLeft'],7),'#fbe3e4',if(lessOrEquals(item()?['DaysLeft'],30),'#ffe2b3','#fff4c2')))

The first cell shows OVERDUE, TODAY or the number of days left:

if(less(item()?['DaysLeft'],0),concat('OVERDUE ',string(mul(item()?['DaysLeft'],-1)),'d'),if(equals(item()?['DaysLeft'],0),'TODAY',concat(string(item()?['DaysLeft']),' days')))

The full Map expression is a concat() of a tr tag with that background colour, then one td per column: days left, notice deadline, tenant and property, right, effective date and notes. Use formatDateTime(item()?['Notice Deadline'],'MMM d, yyyy') for the dates, so they read like "Oct 5, 2026".

Step 7: Send the digest

Add Office 365 Outlook > Send an email (V2). In the body's code view (</>), add a short intro, a colour legend, a table header row and then all the rows joined together:

@{join(body('Digest_rows'),'')}

A subject line that tells the broker what matters without opening the email:

@{length(body('Open_deadlines'))} lease notice deadlines in the next 90 days@{if(greater(length(body('Overdue')),0),concat(' (',length(body('Overdue')),' overdue)'),'')}

Set Importance to High only when something is overdue:

if(greater(length(body('Overdue')),0),'High','Normal')

Step 8: Test it

Save the flow and choose Test > Manually. With our sample data on September 30, the digest listed six deadlines. The Northwind Logistics renewal option had passed 4 days earlier, so it was at the top in red. Brightpath Tutoring's termination right was 5 days out. The Lakeshore Fitness renewal was left off because its notice had already been sent, and deadlines more than 90 days away stayed off until their turn.

Mark a row's Notice Sent as Yes, run it again, and that row disappears.

Ideas to take it further

• Send each deadline to the responsible broker: add a Broker Email column, then group the rows by broker and send one digest each.

• Add a "notice draft" link: store a Word template per right type in SharePoint and link it from the email so the notice letter is one click away.

• Weekly client copies: filter by tenant and send each client a short list of their own upcoming rights, a simple way to show value between deals.

• Holidays and business days: if a lease requires notice by business days, subtract weekends and statutory holidays with a Holidays table.

• Add Teams: post the overdue items to a deal team channel as well as the email.

Why this matters

A missed renewal option can mean a client pays thousands more per month in rent, or loses a location they built their business around. A 10-minute flow and a well-kept table are cheap insurance, and they make you the broker who never lets a deadline slip.

Beyond the spreadsheet

This flow covers the dates. If you want the whole picture, Frontage is our commercial leasing CRM for Canadian brokers. It imports the lease list you already keep, surfaces leases at the milestones you choose, and turns each one into a deal with the tours, offers and LOIs kept together. Starter is free, with no credit card.

Need help automating your own brokerage workflows? Smart Solutions builds Power Automate and Power Apps solutions for Canadian real estate teams. Contact us to talk about what you'd like to automate.