Data Validation - Time Range - 15 Minute Intervals

[JugglerJAF]

I have a range of cells in which I need users to input a time (input as hh:mm) between 06:00 and 22:00, but the times can be at 15 minute intervals betweenthose times (i.e. 06:00, 06:15, 06:30 etc.)

I've set up a "valid entries" list containing those values, assigned that range a name and used that name as the source for a Data Validation list, but even though the entries I'm typing are in the range of valid entries, I sometimes (but not always) get an error message when I type in certain times.

Is there a better/easier/more reliable way of setting up this validation?

 

[Andrew Poulsom]

Maybe try Allow Custom, Formula:

=AND(A1>=TIMEVALUE("06:00"),A1<=TIMEVALUE("22:00"),MOD(MINUTE(A1),15)=0)

where A1 is the Data Validation cell.