You're building a single named LAMBDA called OVERDUE that takes a due date and a threshold in days, and returns one of four labels: blank, "On Track", "Due Soon", or "Overdue". You'll then call it from three different tables in the same workbook - an invoice register, a project task list, and a compliance review log - each with its own threshold. By the end you'll have one place to change the rule and three places that follow.
You'll need Excel for Microsoft 365 (LAMBDA and LET are required) and any workbook with three tables that have a "due date" column. If you don't have one, scaffold one. The point is the pattern, not the data.
Why this approach
The default move is to write a nested IF in the first table, copy it into the second, copy it into the third, and slightly tweak each one because the threshold's different. Six months from now, someone changes how "Due Soon" is defined, you fix it in two places, miss the third, and the compliance team finds out at audit.
A named LAMBDA in Name Manager fixes the architecture problem. The logic lives in one place, scoped to the workbook. The three tables call it. Change the rule once, all three update. The cell formulas are short enough to read in a glance.
This is the part that LAMBDA changes about formula work: you stop writing the same logic in different places and start designing small, named functions that the workbook calls. The "Excel architecture" framing is not overblown. It's the right word for what's happening.
Step 1: Define the OVERDUE LAMBDA in Name Manager
Formulas tab > Name Manager > New. In the dialog:
- Name:
OVERDUE - Scope: Workbook
- Refers to:
=LAMBDA(due_date, threshold_days,
LET(
days_past, TODAY() - due_date,
IF(due_date = "", "",
IF(days_past < 0, "On Track",
IF(days_past <= threshold_days, "Due Soon",
"Overdue")))
)
)Click OK. The name now exists at workbook scope, callable from any cell on any sheet.
Test it in any empty cell:
=OVERDUE(TODAY() + 3, 7)Should return "Due Soon". The date is 3 days in the future, the threshold is 7 days, so the date falls inside the warning window. Try OVERDUE(TODAY() - 10, 5) and you should get "Overdue".
If the parameter names look unusual, that's because LAMBDA parameter names can't start with a number and can't collide with cell references (no A1, R1C1, etc.). due_date and threshold_days are safe and self-documenting.
Step 2: Call it from the Invoices table
Your Invoices table has columns [Customer], [InvoiceDate], [DueDate], [Amount]. You want a [Status] column with a 7-day threshold - anything within 7 days of today is "Due Soon".
In the [Status] column, first row:
=OVERDUE([@DueDate], 7)Press Enter. The structured table fills down automatically. Every row evaluates against today's date with a 7-day window.
Step 3: Call it from the Tasks table with a different threshold
Your Tasks table tracks project work and has [TaskName], [Assignee], [DueDate], [Priority]. Tasks have a longer runway. You want "Due Soon" to fire 14 days out.
In a new [Status] column:
=OVERDUE([@DueDate],14)Same function, different threshold. The cell formula is identical except for the second argument. No copy-pasted logic, no nested IF to maintain.
Step 4: Call it from the Compliance table
Your Compliance table has [Control], [Owner], [NextReviewDate], [LastReviewedBy]. Compliance reviews want a much wider window - 30 days, because chasing controls is slow work.
=OVERDUE([@NextReviewDate], 30)Three tables, three thresholds, one function.
Step 5: Change the rule in one place
Here's the part that shows why this matters. Suppose the team decides "Due Soon" should also surface anything overdue by less than 3 days, with a softer label "Just Past Due" before it escalates to "Overdue". You don't want to touch three tables.
Open Name Manager. Edit OVERDUE. Update the formula:
=LAMBDA(due_date, threshold_days,
LET(
days_past, TODAY() - due_date,
IF(due_date = "", "",
IF(days_past < 0, "On Track",
IF(days_past <= threshold_days, "Due Soon",
IF(days_past <= 3, "Just Past Due",
"Overdue"))))
)
)Click OK. Every cell in every table that calls OVERDUE re-evaluates against the new logic. Invoices, Tasks, Compliance - all three update without a single cell formula being touched.
This is the architecture shift. The status logic became a contract, not a copy.
Common mistakes
Naming the parameter the same as a column header.
Tempting to call the parameter DueDate to mirror the table. Don't. If any sheet has a defined name DueDate or a column reference resolves to it, the LAMBDA will collide. Use parameter names that are clearly local: due_date, threshold_days. Underscore-separated lowercase keeps them visually distinct from column references.
Hardcoding the threshold inside the LAMBDA.
If you bake 7 into the formula and remove the second parameter, you've built a single-purpose function. The reusability comes from accepting the threshold as an argument. Make every value that varies between callers a parameter. This is the same rule that applies in any other language - it sounds new in Excel because the surface is different.
Forgetting TODAY() recalculates on every open.
TODAY() is volatile - the status changes when you open the workbook the next day. That's almost always what you want for an "is this overdue" label, but it's worth knowing. If you need a snapshot status as of a specific date, pass that date in as a third parameter and replace TODAY() with it.
Writing the LAMBDA in a cell instead of registering it in Name Manager.
A LAMBDA defined inline in a cell only works in that cell. The point of Name Manager is to lift the function up to workbook scope so any sheet can call it. If you find yourself rewriting the LAMBDA in cells, you've skipped the architecture step.
Defining the same LAMBDA in multiple workbooks.
If you're using OVERDUE across many files, distribution becomes a real problem. Excel doesn't have a native module system for LAMBDAs, so the practical answer is a template workbook with the LAMBDAs pre-loaded, or a small library file you copy out of when starting something new. There's no clean cross-workbook import - this is a genuine limitation.
Frequently Asked Questions
What is a LAMBDA function in Excel?
LAMBDA is an Excel function that lets you define your own reusable functions without writing code. You write the logic once, give it a name via Name Manager, and call it from any cell using that name. The function is scoped to the workbook by default.
Can I share a LAMBDA across multiple workbooks?
Not directly. Excel doesn't have a native module system for LAMBDAs, so the only practical patterns are: keep a template workbook with the LAMBDAs pre-loaded, or copy the LAMBDA definition out of Name Manager when starting a new workbook. Microsoft has signalled interest in cross-workbook function libraries, but for now you're managing distribution by hand.
Why use Name Manager instead of writing the LAMBDA in a cell?
Writing a LAMBDA into a cell creates a function that's only callable from that cell. Defining the LAMBDA in Name Manager promotes it to workbook scope, where any cell on any sheet can call it by name. The Name Manager entry is the architectural step that turns a one-off formula into a reusable function.
Does TODAY() update automatically in a LAMBDA?
Yes. TODAY() is volatile, so any LAMBDA that uses it recalculates whenever the workbook is opened or recalculated. The status labels returned by an OVERDUE LAMBDA roll forward correctly without manual intervention. If you need a snapshot status as of a specific date, take TODAY() out of the LAMBDA and pass the date in as a parameter.
Can a LAMBDA call other LAMBDAs?
Yes. LAMBDAs at workbook scope can call each other by name. This is how you compose larger pieces of logic from small, named, single-purpose functions - the same pattern any developer would recognise from designing functions in code.