Function Problems

Create Excel Data Validation for Month and Quarter Formats

Ayan MasoodAyan Masood Nov 22, 2026 868 views

Question details

Create a validation rule that accepts month entries for 2018 and quarter entries for 2019 through 2021.

Create Excel Data Validation for Month and Quarter Formats
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.
Before you start

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.

Solution 1Recommended

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.

1
Format the cells as text

Select the target range, such as A2:A100, and choose Home > Number Format > Text before entering the values.

2
Open data validation

With the range selected, choose Data > Data Validation. On the Settings tab, set Allow to Custom.

3
Enter the formula

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")))

4
Set the error alert

On the Error Alert tab, enable the alert and choose Stop to reject invalid typed entries. Click OK to apply the rule.

5
Test 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.

Apply a Custom Data Validation Formula in Excel
Check existing and pasted values: Formatting an existing date cell as Text does not convert its stored date into the original text entry. Data validation may also be bypassed when values are pasted, so check existing and pasted entries separately.
WPS Spreadsheets

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. 1. Open the spreadsheet: Open your workbook in WPS Spreadsheets and select the cells where users will enter month or quarter values.
  2. 2. Set the text format: Choose Home > Number Format > Text before entering values in the selected cells.
  3. 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. 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.
Apply one custom rule to both month and quarter entries.Open and edit Microsoft Excel XLSX files.Use familiar spreadsheet menus and formulas.Work with a free, lightweight office suite.
microsoft office alternative - wps office

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.