Google Sheets特定日期时间格式的数据验证实现问询

Absolutely! You can use Data Validation paired with a custom regular expression to lock in your exact datetime format (01 Apr at 11:00 style) and flag invalid entries with that red corner triangle you mentioned. Here's how to set it up step by step:

• First, select the range of cells where you want this validation applied.
• Go to the menu bar: Data > Data validation
• In the "Criteria" dropdown, choose Custom formula is
• Paste this regex formula into the input box (replace A1 with the top-left cell of your selected range if needed):

  =REGEXMATCH(A1, "^(0[1-9]|[12][0-9]|3[01]) (Jan|Feb|Mar|Apr|May|Jun|Jul|Aug|Sep|Oct|Nov|Dec) at ([01][0-9]|2[0-3]):[0-5][0-9]$")
  

• Let me break down what this regex does to match your requirements:
  • ^(0[1-9]|[12][0-9]|3[01]): Ensures dates are two digits (01-31) with a leading zero for single-digit days (so no "1" instead of "01")
  • (Jan|Feb|Mar|Apr|May|Jun|Jul|Aug|Sep|Oct|Nov|Dec): Strictly matches only the 3-letter English month abbreviations you specified
  • at: Explicitly matches the literal " at " (with spaces on both sides) as your separator
  • ([01][0-9]|2[0-3]):[0-5][0-9]: Validates 24-hour time (00-23 for hours, 00-59 for minutes), both as two-digit values
  • ^ and $: Makes sure the entire cell content follows the format—no extra characters before or after
• Next, set up the error handling:
  • Under "On invalid data", select Reject input (this will block incorrect entries and show the red triangle)
  • Optional: Check "Show validation help text" and add a message like "Please use format: DD Mmm at HH:MM (e.g., 01 Apr at 11:00)" to guide users
• If you want to allow empty cells without flagging them as invalid, modify the formula to:

  =OR(A1="", REGEXMATCH(A1, "^(0[1-9]|[12][0-9]|3[01]) (Jan|Feb|Mar|Apr|May|Jun|Jul|Aug|Sep|Oct|Nov|Dec) at ([01][0-9]|2[0-3]):[0-5][0-9]$"))
  

Test it out by typing valid entries (like 05 Oct at 23:59) and invalid ones (like 5 Apr at 11:0 or 01 January at 10:00)—you’ll see the red triangle appear right away for any mismatches. This method is perfect for keeping your data consistent!

内容的提问来源于stack exchange，提问作者Jan Nieboras