Data validation using regular expressions in Google Sheets

Question

I am using the below date/time format in gSheets:

01 Apr at 11:00

I wonder whether it is possible to use Data Validation (or any other function) to report error (add the small red triangle to the corner of the cell) when the format differs in any way.

Possible values in the given format:

• 01 -> any number between 01-31 (but not "1", there must be the leading zero)
• space
• Apr -> 3 letters for month (Jan, Feb, Mar... Dec)
• space
• at
• space
• 11 -> hours in 24h format (00, 01...23)
• :
• 00 -> minutes (00, 01,...59)

Is there any way to validate that the cell contains "text/data" exactly in the above mentioned format?

Answer 1

The right way to do this is using Regular Expression and "regexmatch()" function in Google Sheets. For the given example, I made the below regular expression:

[0-3][0-9] (Jan|Feb|Mar|Apr|May|Jun|Jul|Aug|Sep|Oct|Nov|Dec) at [0-2][0-9]\:[0-5][0-9]

Process:

• Select range of cells to be validated
• Go to Data > Data Validation
• Under Criteria select "Own pattern is" (not sure the exact translation used in EN)
• Paste: =regexmatch(to_text(K4); "[0-3][0-9] (Jan|Feb|Mar|Apr|May|Jun|Jul|Aug|Sep|Oct|Nov|Dec) at [0-2][0-9]\:[0-5][0-9]")
• Make sure that instead of K4 in "to_text(K4)" there is a upper-left cell from the selected range
• Save

Hope it helps someone :)

Answer 2

You may try the formula for data validation:

=not(iserror(SUBSTITUTE(A1," at","")*1))*(len(A1)=15)*(right(A1,2)*1<61)

• not(iserror(SUBSTITUTE(A1," at","")*1)) checks all statemant is legal date
• (len(A1)=15) checks dates are entered with 2 digits
• (right(A1,2)*1<61) cheks too much minutes, for some reason 01 Apr at 11:99 is a legal date..