Allow date greater than today in a cell
This tutorial shows how to only allow entry of dates in a cell that are greater than the current date using Excel or VBA
Select a cell > Data tab > Data Tools group > Click on Data Validation > Data Validation > Select Settings tab > Select Date > Select greater 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 greater than the current date. Note: in this example we are selecting range B2. |
 |
| 2. Select the Data tab. |  |
| 3. Click on Data Validation in the Data Tools group. 4. Select Data Validation. |
 |
| 5. Select the Settings tab. 6. Select Date in the Allow input box. 7. Select greater than in the Data input box. 8. Enter =NOW() in the Start date input box. 9. Click OK |
 |
METHOD 2. Allow date greater 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 greater than the current date. Note: in this example we are selecting range B2. |
|
| 2. Select the Data tab. | |
| 3. Click on Data Validation in the Data Tools group. 4. 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 |
 |
METHOD 1. Allow date greater than today in a cell
VBA
Sub Allow_date_greater_than_today()
'declare variables
Dim ws As Worksheet
Dim Rng As Range
Dim ws As Worksheet
Dim Rng As Range
Set ws = Worksheets("Analysis")
Set Rng = ws.Range("B2")
Set Rng = ws.Range("B2")
'apply data validation
With Rng.Validation
With Rng.Validation
.Add Type:=xlValidateDate, Operator:=xlGreater, 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 greater than the current date by changing the cell reference ("B2") in the VBA code.
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 greater than the current date by changing the cell reference ("B2") in the VBA code.
EXPLANATION
This tutorial shows how to only allow entry of dates in a cell that are greater 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 greater than the current date. The first method uses the Excel built-in date data validation option (greater than) and can be completed in nine steps. The second method is achieved through the use of a formula (=cell>NOW()) in the Data Validation dialog box and can be completed in eight steps.
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 greater than the current date.
RELATED TOPICS
| Related Topic | Description | Related Topic and Description |
|---|---|---|
| Allow date less than today in a cell | How to only allow entry of dates in a cell that are less than the current date | |
| Limit number of characters in a cell | How to allow to only enter n number of characters in a cell |