Restrict a Date to the Past or the Future (Formula)

Q: What does this formula do?

Date must be after today. It runs as column validation, so it checks the value when the item is saved and blocks entries that do not pass.

Q: Which columns do I need to set this up?

You need: Renewal Date (Date). The formula reads these to produce its result.

Q: Will it check items that are already in the list?

No. Validation applies only to new or edited entries. Existing items are not re-checked when you add the rule.

Q: 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.

Q: How do I stop someone entering a birthdate in the future?

Use =[Birthdate] on the birthdate column. It is the same rule as the future-only version with the operator flipped.

Q: Does TODAY() keep the rule accurate over time?

It is evaluated when an item is saved, so new and edited items are always checked against the current date. Items already in the list are never re-checked, so a date that was valid last year stays put.

This column validation uses a SharePoint formula to keep a date on the right side of today, so a renewal cannot be booked in the past and a birthdate cannot be set in the future. Because it lives in validation, SharePoint enforces it for every item automatically - no custom development and nothing for your users to remember.

Built with

Features

Column Validation, Date and Time Column

Common Use Cases

Renewal or expiry dates must be ahead
Scheduling future events
Preventing back-dated deadlines
Forward-looking planning fields

Details

Formula Type: Column Validation
Formula Category: Dates & Time

Required Columns

Display name	Column type
Renewal Date	Date and Time

Prerequisites

Create the column the formula reads: Renewal Date
Add or edit a few items so there is data for the formula to work with

How to Apply the Formula

Open the column you want to validate (Edit column, then More options)
Expand Column validation
Paste the formula below into the Formula box
Add a user message that explains the rule, so people know why an entry was blocked
Save. From now on, new or edited entries must pass the rule

Formula

=[Renewal Date]>TODAY()

Variations

Future dates only: =[Renewal Date]>TODAY()
Past dates only: =[Birthdate]





Today or later: =[Due Date]>=TODAY()



Inside a window: =AND([Date]>=TODAY(),[Date]<=TODAY()+90)





Common Questions About Restrict a Date to the Past or the Future Formula



What does this formula do?
Date must be after today. It runs as column validation, so it checks the value when the item is saved and blocks entries that do not pass.




Which columns do I need to set this up?
You need: Renewal Date (Date). The formula reads these to produce its result.




Will it check items that are already in the list?
No. Validation applies only to new or edited entries. Existing items are not re-checked when you add the rule.




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.




How do I stop someone entering a birthdate in the future?
Use =[Birthdate] on the birthdate column. It is the same rule as the future-only version with the operator flipped.





Does TODAY() keep the rule accurate over time?
It is evaluated when an item is saved, so new and edited items are always checked against the current date. Items already in the list are never re-checked, so a date that was valid last year stays put.



		

						

								
					
					

						

					

										
						Allow Future Dates Only Formula Example