How To Generate The Pattern To Validate Input Using Regular Expressions Dates In Excel?


Generate the pattern to validate entry dates a column or row default is something that can be achieved through the macro code that exists in Excel , so make the remaining information in a predetermined format.

For this, regular expressions are used. They define themselves as looking models in the text matches the person entering into the sheet.

How to Generate pattern to validate Entering dates Using Regular Expressions in Excel

These models encompass all types of characters that are placed during the encoding Visual Basic to symbolize an error if there is a change in content.

In this way, we explain the group of characters that shape and how you can write the pattern that lets you validate the dates you enter in Excel .

What is the pattern that you set for dates?

generate the pattern to validate entry dates you know what symbols, numbers and letters write for the code to be read correctly and the function is executed as expected.

The code must establish within the module is as follows:

  • “^ ([0] [1-9] | [12] [0-9] [3 [01]) (\/| -) ([0] [0-9] | [1 ] [0-2]) \ 2 (\ d {4}) $ “

The symbol disjunction ^ ” should always go to the beginning of the chain, which is responsible for indicating that can be given one or several sequences, but not all at the same time.

Definition of the code set to generate the pattern to validate entry dates

Because the dates are in the form of day, month and year and have a degree of variability, this is what will set the number of sequences to be written in code.

 green Excel icon bubble

Days of the month

For the day, we know that in the calendar have a variation from 0 to 30 (or 31 depending on the month), so it is arming need three types of sequences:

You may also be interested in:

  • The first covering numbers starting with number 0 and can culminate in 1 to 9.
  • The second covering numbers starting with number 1 and may culminate in 0 to 9.
  • The third and last, covering numbers starting with number 3 and culminate between 0 and 1.

To set sequences. Parentheses are open to write each of the sequences in brackets to set the main number and the number that will be accompanied to create the format day .

Month Year

After completing the above, Valid separators are placed that can be entered by the person, in the case of symbols “/o – brackets , started with a backslash.

In the months sequences are set the same way that the days of the week, setting both sequences with numbers that can range from 0 to 12.

  • · The first sequence will be established with numbers starting with 0 and can culminate in 1 to 9.
  • · The second sequence will be established with numbers beginning with 1 and may culminate in 0 to 2.

Again you re-enter the separators in the same way you did for the first time. Or just add “ \ 2 ” indicating what type must match the second group set in the macro code.

Year Date

the latter part have two options. Either add the sequences in the same way that you can cover a number of years for the dates you will be written.

 cartoon diagrams excel

Because this requires a lot of numbers. The process is accelerated by using brackets placing the term “ \ d ” meaning any number decimal existing.

Since we do not consider any serves infinite number must be set to be limited to a format using four characters “ {4} “.

Finally, it indicates that the sequences have finished adding the symbol “ $ ” and presto!

If you are interested in working properly in Excel. We recommend that you know all the functions that this program offer and make your job easier. If you’re working with graphics learn to make a legend or crear graphics using macros.

You may also be interested in:


If you want to keep private and hide an Excel also can do using macros.

How useful did you find this content?

Click on a star to rate!

Average score 0 / 5. Counting of votes: 0

So far, no votes. Be the first to rate this content.

I'm sorry this content was not useful for you!

Let me improve this content!

Tell me, how can I improve this content?

Deja un comentario