Generating the pattern to validate the entry of dates in a predetermined column or row is something that can be achieved through the macro code that exists in Excel, in order to ensure that the information remains in a predetermined format.
For this, regular expressions are used. They define themselves as models that look for matches in the text that the person enters inside the sheet.
These patterns encompass all kinds of characters that are placed during Visual Basic coding to symbolize an error in the event of a content variance.
In this way, we explain the group of characters that make it up and how you can write the pattern that will allow you to validate the dates you enter in Excel.
What is the pattern you should set for the dates?
When generating the pattern to validate date input, you must know which symbols, numbers and letters you will write so that the code is read correctly and the function is executed as expected.
The code that you must establish inside the module is the following:
- “^([0][1-9]|[12][0-9][3[01]) (/|-) ([0][0-9]|[1][0-2]) 2(d{4})$”
The disjunction symbol “^” must always go at the beginning of the string, which is responsible for indicating that one or more sequences can occur, but not all at the same time.
Definition of the code established to generate the pattern to validate the entry of dates
Since dates come in the form of day, month, and year and have a degree of variability, this is what will establish the number of sequences to be written in the code.
Days of the month
In the case of days, we know that in the calendar they have a variation from 0 to 30 (or 31 depending on the month), so it is necessary to create three types of sequences:
- The first one that includes numbers that start with the number 0 and can end in 1 to 9.
- The second that covers numbers that start with the number 1 and can end in 0 to 9.
- The third and last, which covers numbers that start with the number 3 and end between 0 and 1.
To set the sequences. Parentheses are opened to write each of the sequences between brackets to establish the main number and the number by which it will be accompanied to create the day format.
Month of the year
Once the above is finished, the valid separators that can be entered by the person will be placed, in the case of the “/ or –” symbols in parentheses, starting with an inverted slash.
With the months, the sequences are established in the same way as the days of the week, establishing both sequences with numbers that can range from 0 to 12.
- · The first sequence will be established with numbers that start with 0 and can end in 1 to 9.
- · The second sequence will be established with numbers that start with 1 and can end in 0 to 2.
Again you must re-enter the separators the same way you did it the first time. Or simply add “2” indicating that what is written must match the second group established in the macro code.
date year
In this last part you have two options. Either add the sequences in the same way that you can span a large number of years for the date to be written.
Because this requires a lot of numbers. The process is streamlined by using parentheses around the term “d” which means any existing decimal number.
Since it does not help us to consider any infinite number, it must be established that they will be limited to a format of four characters by means of “{4}”.
Finally, indicate that you have finished the sequences by adding the “$” symbol and that’s it!
If you are interested in working correctly in Excel. We recommend that you get to know all the functions that this program offers you and that make your work easier. If you are going to work with graphics, learn to make a legend or create graphics using macros.
If you want to save privately and hide an Excel sheet, you can also do it through macros.