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

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

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

Assuming that only times will be entered...

Try this...

Select the cell to validate. Let's assume this is cell B11.
Goto DataValidation
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

--
Biff
Microsoft Excel MVP


"Barbara T" wrote in message
...
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

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

In D11 select Data validationCustom 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
---------------
Jacob Skaria


"Barbara T" wrote:

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

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

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


--
Biff
Microsoft Excel MVP


"Jacob Skaria" wrote in message
...
In D11 select Data validationCustom 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
---------------
Jacob Skaria


"Barbara T" wrote:

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