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.


2 Answers 2


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.


Same of David but validate as time with custom formula:


(assuming that column A contains the times)

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

  • Oh that gave me a thought. I just checked, and it appears that time is stored as a decimal. Dates are integers. But when you simply input time, you get a value between 1 and 0! So I propose this: =TIMEVALUE(A2:A)<1.
    – David Tan
    May 12, 2022 at 5:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.