Create Excel Data Validation for Month and Quarter Formats
Question details
Create a validation rule that accepts month entries for 2018 and quarter entries for 2019 through 2021.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A user wants to restrict a column to MM/YYYY values for 2018 and Q1/YYYY through Q4/YYYY values for 2019–2021.
- Observed behavior
- Valid entries should include 03/2018 and Q1/2019, while entries outside the specified formats or years should be rejected.
Identify the first cell in the range you want to validate; the example below uses A2. Format the target cells as Text before entering values so Excel does not convert month entries into dates.
Apply a Custom Data Validation Formula in Excel
Use one rule to allow two-digit months for 2018 and uppercase quarter codes for 2019 through 2021.
The formula treats quarter format as Q1, Q2, Q3, or Q4 followed by a slash and a four-digit year. It uses A2 as the first cell in the selected range.
Select the target range, such as A2:A100, and choose Home > Number Format > Text before entering the values.
With the range selected, choose Data > Data Validation. On the Settings tab, set Allow to Custom.
In the Formula box, enter the following formula, replacing A2 with the top-left cell of your selected range: =OR(AND(LEN(A2)=7,MID(A2,3,1)="/",RIGHT(A2,4)="2018",OR(LEFT(A2,2)="01",LEFT(A2,2)="02",LEFT(A2,2)="03",LEFT(A2,2)="04",LEFT(A2,2)="05",LEFT(A2,2)="06",LEFT(A2,2)="07",LEFT(A2,2)="08",LEFT(A2,2)="09",LEFT(A2,2)="10",LEFT(A2,2)="11",LEFT(A2,2)="12")),AND(LEN(A2)=7,EXACT(LEFT(A2,1),"Q"),OR(MID(A2,2,1)="1",MID(A2,2,1)="2",MID(A2,2,1)="3",MID(A2,2,1)="4"),MID(A2,3,1)="/",OR(RIGHT(A2,4)="2019",RIGHT(A2,4)="2020",RIGHT(A2,4)="2021")))
On the Error Alert tab, enable the alert and choose Stop to reject invalid typed entries. Click OK to apply the rule.
Type 03/2018 and Q1/2019 into cells in the selected range; both should be accepted. Test 13/2018, Q5/2019, and Q1/2018 to confirm they are rejected.

Validate Month and Quarter Entries in WPS Spreadsheets
Use a custom validation formula in WPS Spreadsheets to restrict entries to 2018 months and 2019–2021 quarters.
- 1. Open the spreadsheet: Open your workbook in WPS Spreadsheets and select the cells where users will enter month or quarter values.
- 2. Set the text format: Choose Home > Number Format > Text before entering values in the selected cells.
- 3. Add the validation rule: Choose Data > Validation, select Custom in the validation settings, and enter the formula from the Excel solution above. Replace A2 with the first cell of your selected range.
- 4. Test the entries: Apply the rule, then type 03/2018 and Q1/2019 to check that they are accepted. Try Q5/2019 to check that an invalid quarter is rejected.

Frequently Asked Questions
Why should I format the cells as Text first?
Excel may interpret an entry such as 03/2018 as a date rather than the seven-character text the formula checks. Select the cells and choose Home > Number Format > Text before entering them.
Can I use the formula in a column other than A?
Yes. Replace each A2 reference in the formula with the top-left cell of your selected range. For example, use C5 if the range starts at C5.
Will the rule accept lowercase quarter codes?
No. The EXACT function requires an uppercase Q, so Q1/2019 is accepted but q1/2019 is rejected.
Does the rule automatically remove invalid values already in the cells?
No. Check existing values separately. In Excel, you can use Data > Data Validation > Circle Invalid Data to help identify values that do not meet the rule.




