Google Sheets Data Validation for Time Values

To validate time entries in Google Sheets, select the cells, choose Data > Data validation > Add rule, and set Criteria to Custom formula is. Use a formula that checks the selected cell contains a numeric time within your allowed range, then choose Reject input to block invalid entries. For example, a rule for times from 9:00 AM through 5:00 PM is shown below.

Why time validation usually needs a formula

Google Sheets does not provide a dedicated time-range criterion in its data-validation menu. Its Date criteria validate dates, not a daily time window such as a shift from 9 AM to 5 PM. A custom formula fills that gap by checking the value in each cell against time boundaries.

Sheets stores a time as part of a day: midnight is 0, noon is 0.5, and 6 PM is 0.75. A date-time value combines a whole-number date with a fractional time. This is why comparing numeric values to TIME results works, and why it matters whether a cell contains just a time or a full date and time.

For an ordinary time-only entry, the formula below accepts the endpoints as well as times between them. With the validation range starting at A2, apply this formula to A2:A:

=AND(ISNUMBER(A2),A2>=TIME(9,0,0),A2<=TIME(17,0,0),A2<1)

The final check, A2<1, limits accepted values to less than one day, so a date-time value is not mistaken for a time-only entry. A valid numeric time such as 9:00 AM or 5:00 PM passes; text that merely looks like a time does not. The formula uses commas as argument separators. Depending on spreadsheet locale, a formula may require semicolons instead.

Set up the rule in Google Sheets

Select the input cells, for example A2:A50. Selecting the whole column is also possible, but excluding a header makes the rule easier to understand and maintain.
Open Data > Data validation, then choose Add rule. In the side panel, confirm the Apply to range field contains the intended cells.
Under Criteria, choose Custom formula is and enter the formula. Use the address of the first cell in the selected range: for A2:A50, refer to A2.
Under the invalid-data setting, choose Reject input if people should not be able to enter a value that fails the rule. Choose Show a warning if the rule should advise rather than block.
Optionally add help text, such as Enter a time from 9:00 AM to 5:00 PM. Select Done to save the rule.

For a custom-formula rule applied to a range, keep the reference to the first cell relative, as in A2. Sheets adjusts that reference for the other rows: the check in row 3 evaluates A3. Locking it as $A$2 would make every row inspect the same cell, which is normally not what an entry rule should do.

Allow blank cells or require an entry

The example rule rejects a blank because a blank is not a numeric time. That is appropriate when every row must be completed, but it can be inconvenient for a form or schedule that is filled in gradually. To allow blanks while still restricting nonblank values, use ISBLANK as an alternative branch.

With the validation range starting at A2, this version accepts an empty cell or a time from 9:00 AM through 5:00 PM, but still excludes date-time values:

=OR(ISBLANK(A2),AND(ISNUMBER(A2),A2>=TIME(9,0,0),A2<=TIME(17,0,0),A2<1))

ISBLANK recognizes a genuinely empty cell. A cell containing a formula that displays an empty string is not truly blank, so this rule will not necessarily treat it as one. If your sheet uses formulas to create visually empty cells, decide whether those cells should be valid and test the rule against that setup.

Handle timestamps and shifts that cross midnight

If entries may include both a date and a time, use MOD(value,1) to check only the fractional time portion. For instance, a scheduled appointment stored as a date-time can still be limited to business hours regardless of its calendar date. With the validation range starting at A2, use:

=AND(ISNUMBER(A2),MOD(A2,1)>=TIME(9,0,0),MOD(A2,1)<=TIME(17,0,0))

Do not add the A2<1 condition in this case: it would reject date-time entries because their serial values include a whole-number date. MOD removes that date portion for the comparison. It does not verify that the date itself is appropriate, so add a separate date check if the date also needs limits.

An overnight window needs an OR condition because times after the evening start or before the morning end are allowed. With A2 as the first cell in the applied range, this example permits 10 PM through 6 AM, including 10 PM but excluding 6 AM:

=AND(ISNUMBER(A2),OR(MOD(A2,1)>=TIME(22,0,0),MOD(A2,1)<TIME(6,0,0)))

The two boundaries are a policy choice. Change >= to > if the start time should not be accepted, or < to <= if the end time should be included. Decide whether an endpoint represents an allowed moment or the beginning of an excluded interval before sharing the sheet.

Format the cells so entries are readable

Validation controls what values are accepted; number formatting controls how accepted values appear. Select the cells and use Format > Number > Time for a standard time display. If you need a particular presentation, use Format > Number > Custom date and time and select an appropriate pattern. Formatting a cell as a time does not itself restrict what someone can enter.

For example, the same underlying value might display as 3:30 PM in one format and 15:30 in another. The format changes the display, not the underlying time being checked. If a value unexpectedly shows a date as well, inspect the number format and determine whether the cell contains a date-time rather than a time-only value.

Time interpretation can also depend on the spreadsheet’s locale. Check File > Settings if date or time input is being read differently than expected. In particular, ambiguous typed values may be interpreted according to local conventions. A clearly specified time format and a brief help message reduce confusion for collaborators.

Reject input, warnings, and existing data

Reject input is the stronger choice for a controlled entry column: a direct edit that fails the rule is blocked. Show a warning permits the value but flags that it falls outside the rule. A warning can be useful during a transition, when exceptions are legitimate, or when users need to record an unusual value and explain it elsewhere.

Adding a rule does not automatically clean up values already in the range. Review existing entries after creating the rule, especially if the sheet has been populated by copy-and-paste, an import, or formulas. Pasted or imported data may not behave like a person typing directly into a validated cell, so do not treat the rule as a complete audit of every value in a large workbook.

For reliable processes, pair validation with a review: sort or filter the entries, inspect suspicious values, or use conditional formatting to highlight exceptions. This is particularly important for a shared schedule where an incorrect time could affect staffing or appointments. A rule helps prevent ordinary entry mistakes, but it is not a substitute for checking the final data.

Common problems and how to fix them

The formula rejects a time that looks correct: Check whether the value is numeric or text. ISNUMBER returns FALSE for text such as a pasted label that only resembles a time. Re-enter it as a time value or correct the source data.
The rule checks the wrong cell: Confirm the formula refers to the top-left cell of the Apply to range. If the range starts at B3, write the test using B3, not A2.
Every row appears to use one result: Check for dollar signs in the reference. A reference such as $A$2 stays fixed; a relative reference such as A2 adjusts as the rule evaluates each row.
An entry outside the daily window is accepted: Check whether the source includes a date. A time-only formula may need the A2<1 guard; a date-time formula should compare MOD(A2,1) instead.
The display seems wrong: Change the number format separately from the validation rule. A display format cannot turn text into a valid numeric time or make a value fall within the allowed range.
A value was accepted despite a rule: Review the rule’s applied range and invalid-data setting, then inspect how the value was added. Existing, pasted, imported, or formula-generated values deserve a separate check.

When to use a dropdown instead

If people may choose only from a small set of fixed times, a dropdown can be easier than typing and validating a range. In the Data validation side panel, choose a dropdown criterion and enter choices such as 9:00 AM, 10:00 AM, and 11:00 AM. A dropdown reduces typing variation and makes the available choices visible. It is less suitable when any minute within a broad window is allowed or when the list changes often.

For a recurring set of allowed slots, you can also place the choices in a separate range and use that range as the dropdown source. Keep the source list maintained and clearly label the cells so collaborators know it is supporting data. A dropdown and a time-range formula solve different problems: one restricts values to listed options; the other checks whether a value falls within a condition.

Frequently Asked Questions

Can Google Sheets validate a time between two hours?
Yes. Use Data > Data validation > Add rule, select Custom formula is, and compare the cell with TIME values. Choose Reject input if invalid entries should be blocked.

Why is there no time option under Data validation?
Google Sheets offers date-related criteria but not a dedicated time-range criterion. A custom formula is the practical way to enforce a daily time window.

Does formatting a cell as Time validate it?
No. Format > Number > Time changes how a value is displayed. Use a validation rule separately to restrict what users can enter.

How can I allow a shift from 10 PM to 6 AM?
Use an OR test: accept times at or after 10 PM, or before 6 AM. Decide whether 6 AM itself should be included and adjust the comparison accordingly.

Can I apply one time rule to an entire column?
Yes. Apply it to the desired range, such as A2:A, and write the custom formula relative to its first cell, A2. Exclude the header row unless the header is meant to be checked too.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.