Set cell to reject wrongly formatted data

Question

I’ve got a “time tracker” Google Sheet where people need to enter a record of a start time and end time; we then have a formula which works out the duration between the two times.

The formula is pretty simple and works fine, but the problem is users of the spreadsheet keep entering the start and end times wrong eg. Instead of start 13:00 end 15:00 they might write start 13:00 end 15.00 (note the dot instead of colon)

When they enter the time format wrong the duration formula breaks.

Is there anyway I can set the input cells to throw and error / reject wrongly inputted data ?

I thought of using drop downs for this, but the user has to be able to enter times to the nearest minute so 24 x 60 = 1440 drop down options wouldn’t be very user friendly.

Answer 1

Data > Data Validation

Then select for the Criteria "Date" and "is valid date". Set On invalid data to reject input.

Now I now that this does have the limitation that they could input a date. But since the sheet was made with time being the only thing to be inputted, it shouldn't be much of a problem?

If you want to specifically validate for time, this question could help.

Answer 2

Same of David but validate as time with custom formula:

=TIMEVALUE(A2:A)>0

(assuming that column A contains the times)

I think that you could investigate even on a validation based on regex, see this