Data Validation

[Perksy_no1]

Hi There,

I'm have some problems with data validation. What im trying to do is stop people over planning a line past the planned finish time.

In column g you put in an amount that generates a finished time in column k

I've tried to put the following validation in column g so that it stops you putting to large an amount in column g based on whether its an early or late shift. Early shift finishes at 14:25 late shift 10:55.

=IF(B15="Early",IF(K15< time(14,25,0),true,false),if(k15< time(10,55,0),true,false))<time(14,25,0),true,false),if(k15<time(10,55,0),true,false))
<time(14,25,0),true,false),if(k15<time(10,55,0),true,false))
<time(14,25,0),true,false),if(k15<time(10,55,0),true,false))
<time(14,25,0),true,false),if(k15<time(10,55,0),true,false))
<time(14,25,0),true,false),if(k15<time(10,55,0),true,false))
<time(14,25,0),true,false),if(k15<time(10,55,0),true,false))


But it wont let me put any value into column g.

Thanks in advance for any help you can offer

Mark

Hi there just to add something else to the post the validation depends on whether in column be there is either early or late shift in there
</time(14,25,0),true,false),if(k15<time(10,55,0),true,false))
</time(14,25,0),true,false),if(k15<time(10,55,0),true,false))
</time(14,25,0),true,false),if(k15<time(10,55,0),true,false))
</time(14,25,0),true,false),if(k15<time(10,55,0),true,false))
</time(14,25,0),true,false),if(k15<time(10,55,0),true,false))
</time(14,25,0),true,false),if(k15<time(10,55,0),true,false))

 

[Chrisdontm]

I don't see how you can restrict the entries like you want to, you may be able to, but, you could use Conditional Formatting

and have the cell in column "G" turn red if they enter the wrong amount.

Try this formula in Conditional Formatting for G15;

=OR(AND(B15="Early",K15>TIME(14,25,0)),AND(B15="Late",K15>TIME(22,55,0)))

(Also, I am figuring that you mean for the late time to be 10:55 at night...)