Excel - Custom data validation

Question

Need to add data validation to conform data being entered into the cells within column to follow a particular format.

Release date needs to be entered in following format to maintain Consistency

1. MM/YYYY when YYYY is 2018 (Month/year for 2018)
2. QQ/YYYY when YYYY is 2019-2021 (Quarter/year for 2019,2020,2021)

Thanks!

[Moved from: Office / Excel / Microsoft Office Programming / Office 2016]

Answer 1

Forgot to write that you need to convert your column to Text by right click on column > Format Cells > Text

As if you enter 03/2018, Excel will immediately convert this to date format. By converting to text, Excel will not convert this to Date.

Answer 2

Below is the Data Validation formula for cell A2.

=IFERROR(IF(ISNUMBER(SEARCH("/",A2)),IF(--RIGHT(A2,4)=2018,AND(--LEFT(A2,2)>=1,--LEFT(A2,2)<=12),IF(AND(--RIGHT(A2,4)<=2021,--RIGHT(A2,4)>=2019),AND(--LEFT(A2,2)>=1,--LEFT(A2,2)<=4)))),FALSE)

1. Select your range. In the formula, in place of A2 you need to put that cell which is top left.
2. Data tab > Data Validation > Choose Custom and put the above formula

Answer 3

Awesome!! That did the trick.

Thanks a bunch!!

Answer 4

Thanks for the prompt reply! Appreciate it.

But the formula does not work with the format I am trying.

Can you share the sample data that can be added using the formula shared?

Thanks in advance!