How to use formula to decide when to apply TIME data validation

[Barbara T]

Hi!

I am trying to set up a spreadsheet where I use data validation to restrict
certain cells to only allow times within a specified range (e.g., 7:00AM to
6:00PM) but ONLY when another cell (A11) contains "DAY SHIFT". If that other
cell (A11) does NOT contain "DAY SHIFT" then I want the validated cells to
still allow only times to be entered but to allow ANY time to be entered.

Is there a way to set this up? I can't see how to restrict when the data
validation occurs when the restrictions are based on TIME.

Thanks so much for any insight you can give!!

Barbara

 

[T. Valko]

Assuming that only times will be entered...

Try this...

Select the cell to validate. Let's assume this is cell B11.
Goto Data>Validation
Allow: Custom
Formula:

=IF(A11="day
shift",AND(B11>=TIME(7,0,0),B11<=TIME(18,0,0)),AND(B11>=0,B11<1))

OK

Note: this also assumes you will be entering time *within* a 24 hour period.
12:00 AM to 11:59 PM

 

[Jacob Skaria]

In D11 select Data validation>Custom> and copy paste the below formula

=AND(A11="Day shift",D11<>"",MEDIAN("07:00 AM",D11,"06:00 PM")=D11)

If this post helps click Yes

 

[T. Valko]

=AND(A11="Day shift",D11<>"",MEDIAN("07:00 AM",D11,"06:00 PM")=D11)

If that other cell (A11) does NOT contain "DAY SHIFT"
then I want the validated cells to still allow only times to
be entered but to allow ANY time to be entered.