Renewal Date Before Expiration
This list validation uses a SharePoint formula to require the renewal date to fall before the expiration date. Applied as list validation, it compares the columns when the item is saved and blocks the entry if the rule is not met.
How It Works
- What it does: Compares the two dates and blocks the save unless the Renewal Date is before the Expiration Date.
- If it passes: Renewal Date is earlier than Expiration Date, so the item saves.
- If it fails: SharePoint stops the save and shows your message, for example: Renewal date must be before the expiration date.
Common Use Cases
- Contract renewal timing
- Policy management
- Preventing late renewals
- Date-order integrity
Details
- Formula Type: List Validation
- Formula Category: Dates & Time
Required Columns
| Display name | Column type |
|---|---|
| Renewal Date | Date and Time |
| Expiration Date | Date and Time |
Prerequisites
- Create the columns the formula reads: Renewal Date and Expiration Date
- Add or edit a few items so there is data for the formula to work with
How to Apply the Formula
- Go to List settings (or Library settings), then Validation settings
- Paste the formula below, which compares the columns
- Add a user message explaining the rule
- Save. New or edited items must satisfy the rule
Formula
=[Renewal Date]<[Expiration Date]Common Questions About Renewal Date Before Expiration Formula
What does this formula do?
Cross-field date rule. It runs as list validation, comparing the columns when the item is saved.
Which columns do I need to set this up?
You need: Renewal Date (Date), Expiration Date (Date). The formula reads these to produce its result.
Why use list validation instead of column validation?
List (or library) validation can compare several columns to each other in the same item, which column validation cannot do.
If LookBook 365 is code-free and out-of-the-box, why does this example use a formula?
A SharePoint formula is native, declarative configuration, not custom development. It only works with the item's own fields and simple logic - it cannot run scripts, reach external services, or change other data, and nothing is deployed to your tenant. It keeps working through Microsoft updates. If you would rather not set this up yourself, this is exactly the kind of thing I configure for clients.
Renewal Date Before Expiration Formula Example