1

I am searching for a solution that would allow users to insert time values only in this format: hh:mm, and reject if something else is inserted. Something similar like data validation for date - Data validation -> Is valid date -> Reject input.

I tried to search and adjust a Regexmatch formula for this one, with no success, but I am open for other suggestions also.

Thank you in advance!

player0
  • 124,011
  • 12
  • 67
  • 124
Igor K
  • 37
  • 6
  • "is valid date" will suffice for timestamps within data validation. It will also accept date time though. – Kevin P. Jan 24 '23 at 21:17
  • That's a good point and a nice workaround, reasonably assuming that no one would be typing date by mistake in time column. – Igor K Jan 24 '23 at 21:31

2 Answers2

0

use custom formula:

=REGEXMATCH(""&A1; "\d{2}:\d{2}")

if you want to block inputs like: 75:99 then try:

=REGEXMATCH(""&A1; "\d{2}:\d{2}")*((A1*1)<=1)
player0
  • 124,011
  • 12
  • 67
  • 124
0

SUGGESTION

Perhaps you can try using a more specific regex such as the one sample from this existing post. Then, apply it your data validation via a custom function using REGEXMATCH function as seen here:

=REGEXMATCH(TO_TEXT(A1),"^([0-1]?[0-9]|2[0-3]):[0-5][0-9]$")

Sample Data Validation Config:

enter image description here

Demo:

E.g. Any text or date formats won't be accepted.

enter image description here

SputnikDrunk2
  • 3,398
  • 1
  • 5
  • 17
  • Hope this one helps. Feel free to reply just in case. – SputnikDrunk2 Jan 25 '23 at 01:43
  • Thank you for the reply and for the provided link! – Igor K Jan 26 '23 at 22:04
  • I have some problems in using this statement with other in the same range. I would like to combine these two statements in data validation: =if(or(B2=$M$2;B2=$M$3);true;false) =REGEXMATCH(TO_TEXT(C2);"^([0-9]|0[0-9]|1[0-9]|2[0-3]):[0-5][0-9]$") Is this possible? Google Sheets deletes a rule when I try to apply other to the same range. – Igor K Jan 27 '23 at 20:07
  • You can try putting these two functions using [IF](https://support.google.com/docs/answer/3093364) like this `=IF(IF(OR(B2=$M$2,B2=$M$3),TRUE,FALSE) = REGEXMATCH(TO_TEXT(C2),"^([0-9]|0[0-9]|1[0-9]|2[0-3]):[0-5][0-9]$"), TRUE, FALSE)`. – SputnikDrunk2 Jan 27 '23 at 22:24