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

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
 
T

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
 
J

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

T. Valko

=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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top