Share via

Excel - Custom data validation

Anonymous
2017-10-31T19:12:18+00:00

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]

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments No comments

Answer accepted by question author

Vijay A. Verma 104.9K Reputation points Volunteer Moderator
2017-10-31T20:42:39+00:00

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.

Was this answer helpful?

1 person found this answer helpful.
0 comments No comments

Answer accepted by question author

Vijay A. Verma 104.9K Reputation points Volunteer Moderator
2017-10-31T19:40:48+00:00

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

Was this answer helpful?

1 person found this answer helpful.
0 comments No comments

2 additional answers

Sort by: Most helpful
  1. Anonymous
    2017-10-31T20:53:32+00:00

    Awesome!! That did the trick.

    Thanks a bunch!!

    Was this answer helpful?

    0 comments No comments
  2. Anonymous
    2017-10-31T20:32:30+00:00

    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!

    Was this answer helpful?

    0 comments No comments