To check time input in a range of cells or a single cell you can use the TIMEVALUE function in Google Sheets. If it’s a range of cells the formula should be entered as an Array Formula.

We can use either of the functions ISBLANK or LEN to test whether a cell contains a value. Both will work in a range of cells (array) also.

While the former returns TRUE or FALSE Boolean values the latter returns 0 for blank and the number of characters for non-blanks.

But none of them is useful to test whether the cell not only contains a value but also that value is time.

With TIMEVALUE function we can check time input in a range of cells in Google Sheets.

How to Check Time Input in a Range of Cells Using TIMEVALUE

Let’s start with a single cell.

As you can see the TIMEVALUE formula returns #VALUE! error when the value in cell A1 changes to the text string or number. It only works with date and time.

If the value is a date, the TIMEVALUE formula would return 0. For time input, no doubt, the formula would return the value of that time.

We can use TIMEVALUE in an IF statement as below.

=if(timevalue(A1)>0,TRUE,FALSE)

The above formula would only return TRUE, when the value in cell A1 is a time. For a date input, it would return FALSE. Else for text and number entry, the formula would definitely return the VALUE! error.

Here is a real-life example to the use of checking time input in a range of cells in Google Sheets.

Suppose cell A2 contains the start time and B2 contains end time. I want to only do the below calculation if both the cells contain time inputs.

=B2-A2

Here is the formula that tests both the cells for time inputs and perform the above calculation.

=ArrayFormula(IF(COUNT(TIMEVALUE({A2,B2}))=2,B2-A2,""))

Logic:

The count of cell A2 and B2 will be 2 if both the values are time. Else return 1 or 0.

=ArrayFormula(COUNT(TIMEVALUE({A2,B2})))

That’s all. Hope you have enjoyed the stay!

Additional Resources:

1. How to Filter Timestamp in Query in Google Sheets.
2. Google Sheets: The Best Overtime Calculation Formula.
3. COUNTIFS in a Time Range in Google Sheets [Date and Time Column].
4. How to Convert Military Time in Google Sheets.
5. Elapsed Days and Time Between Two Dates in Google Sheets.