Limit date to multiple years in a cell
This tutorial shows how to limit entry into a cell for only specific years using Excel or VBA
METHOD 1. Limit date to multiple years 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 entry for only specific years. Note: in this example we are selecting cell 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 =OR(YEAR(B2)=2018,YEAR(B2)=2017) in the Formula input box. 8. Click OK |
 |
METHOD 1. Limit date to multiple years in a cell
VBA
Sub Limit_date_to_multiple_years()
'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:=xlValidateCustom, Formula1:="=OR(YEAR(B2)=2018,YEAR(B2)=2017)"
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 dates that have specific years 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 dates that have specific years by changing the cell reference ("B2") in the VBA code.
EXPLANATION
This tutorial shows how to only allow entry of dates that have specific years using Excel or VBA.
This tutorial provides one Excel method that can be applied to only allow entry of dates that have specific years. It uses a custom formula (=OR(YEAR(B2)=year1,YEAR(B2)=year2)) in the Data Validation dialog box and can be completed in eight steps. You can nominate any years by replacing 'year1' and 'year2' in the formula above. You can also allow entry of more years by inserting YEAR(B2)=year into the formula, as another OR criteria. In this example we have restricted to only 2018 and 2017 years.
The VBA code in this tutorial uses a Validation function with the xlValidateCustom validation type to only allow entry of dates that have specific years.
RELATED TOPICS
| Related Topic | Description | Related Topic and Description |
|---|---|---|
| Limit date to a specific year in a cell | How to only allow entry of dates that have a specific year | |
| Limit date to a specific day in a cell | How to only allow entry of dates that have a specific day | |
| Limit date to a specific month in a cell | How to only allow entry of dates that have a specific month | |
| Limit date to a specific year and month in a cell | How to only allow entry of dates that have a specific year and month | |
| 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 |