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

  • Thread starter Thread starter Barbara T
  • Start date Start date
B

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
 
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
 
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
 
=AND(A11="Day shift",D11<>"",MEDIAN("07:00 AM",D11,"06:00 PM")=D11)

That doesn't allow for entry of *any time* when A11 said:
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.
 
Back
Top