Allow date less than today in a cell

This tutorial shows how to only allow entry of dates in a cell that are less than the current date using Excel or VBA

METHOD 1. Allow date less than today in a cell using an Excel built-in option

EXCEL

Select a cell > Data tab > Data Tools group > Click on Data Validation > Data Validation > Select Settings tab > Select Date > Select less than > Enter =NOW() > Click OK

1. Select a cell in which you want to limit users to only be able to enter a date that is less than the current date.
Note: in this example we are selecting range B2. 		Select a cell

2. Select the Data tab. Select the Data tab

3. Click on Data Validation in the Data Tools group.
4. Select Data Validation. 		Click on Data Validation and select Data Validation

5. Select the Settings tab.
6. Select Date in the Allow input box.
7. Select less than in the Data input box.
8. Enter =NOW() in the Start date input box.
9. Click OK 		Select Settings tab, select Date, select less than, enter NOW() and click OK

METHOD 2. Allow date less than today in a cell using a formula

EXCEL

Select a cell > Data tab > Data Tools group > Click on Data Validation > Data Validation > Select Settings tab > Select Custom > Enter formula > Click OK

1. Select a cell in which you want to limit users to only be able to enter a date that is less than the current date.
Note: in this example we are selecting range B2. 		Select a cell

2. Select the Data tab. Select the Data tab

3. Click on Data Validation in the Data Tools group.
4. Select Data Validation. 		Click on Data Validation and select Data Validation

5. Select the Settings tab.
6. Select Custom in the Allow input box.
7. Enter the formula =B2<NOW() in the Formula input box.
8. Click OK 		Select Settings tab, select Custom, enter formula and click OK

METHOD 1. Allow date less than today in a cell

VBA

Sub Allow_date_less_than_today()
'declare variables
Dim ws As Worksheet
Dim Rng As Range
Set ws = Worksheets("Analysis")
Set Rng = ws.Range("B2")
'apply data validation
With Rng.Validation

.Add Type:=xlValidateDate, Operator:=xlLess, Formula1:="=NOW()"

End With

End Sub

ADJUSTABLE PARAMETERS
Worksheet Selection: Select the worksheet in which you want to apply a restriction to a cell by changing the Analysis worksheet name in the VBA code. You can also change the name of this object variable, by changing the name 'ws' in the VBA code.
Cell: Select the cell in which you want to only allow entry of a date that is less than the current date by changing the cell reference ("B2") in the VBA code.

Explanation about how to allow date less than today in a cell

EXPLANATION

EXPLANATION

This tutorial shows how to only allow entry of dates in a cell that are less than the current date using Excel or VBA.
This tutorial provides two Excel methods that can be applied to only allow entry of dates in a cell that are less than the current date. The first method uses the Excel built-in date data validation option (less than) and can be completed in nine steps. The second method is achieved through the use of a formula (=cell
The VBA code in this tutorial uses a Validation function with the xlValidateDate validation type to only allow entry of dates in a cell that are less than the current date.

RELATED TOPICS

Related Topic Description Related Topic and Description
Allow date greater than today in a cell How to only allow entry of dates in a cell that are greater than the current date
Limit number of characters in a cell How to allow to only enter n number of characters in a cell