custom data validation
 

I have a sheet with 6 columns of dates (K to P). K is the job start date, P
is the job end date and the ones inbetween are inbetween dates. It is
possible for some of the ones inbetween not to need to have a date in them
(left blank).
What I want to do is validate the cells so that the user cannot enter
incorrect dates. For example, enter an end date which is before the start
date. I have managed to validate just start date against end date but when
it comes to a formula to include all the columns I get lost.
PS
The entry in the cell must be greater than 01/01/2007 and today

custom data validation
 

"Frank Pytel" wrote:

Gareth;

Have you considered Conditional Formatting. This will not prevent the user
from entering information, but the code is a little lighter on your
application. Select the Colum P cell(s) that you want alerted and Go To:

Format Conditional Formatting.

From the drop down menu select Formula and enter the following code:

=if(K1P1,True,False)

Click on the Format button and choose what you want to happen when this is
true. The formula says that if the Start Date comes before the End Date,
turn the "cell Red", "Font Green/Bold" or whatever else you desire.

God Bless

Frank Pytel


Frank

I do need to use data validation as cond formatting will let users enter the
incorrect date even though they have been warned about it.

Gareth

custom data validation
 

You don't need the IF(...,TRUE,FALSE) , Frank.

Instead of
=if(K1P1,True,False)
you can use
=K1P1
as that will return a Boolean result.
--
David Biddulph

"Frank Pytel" wrote in message
...
Gareth;

Have you considered Conditional Formatting. This will not prevent the user
from entering information, but the code is a little lighter on your
application. Select the Colum P cell(s) that you want alerted and Go To:

Format Conditional Formatting.

From the drop down menu select Formula and enter the following code:

=if(K1P1,True,False)

Click on the Format button and choose what you want to happen when this is
true. The formula says that if the Start Date comes before the End Date,
turn the "cell Red", "Font Green/Bold" or whatever else you desire.

God Bless

Frank Pytel

custom data validation
 

Select L2:On and in DV use a formula of

=AND(L2$K2,L2<P2)

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Gareth" wrote in message
...
I have a sheet with 6 columns of dates (K to P). K is the job start date,
P
is the job end date and the ones inbetween are inbetween dates. It is
possible for some of the ones inbetween not to need to have a date in them
(left blank).
What I want to do is validate the cells so that the user cannot enter
incorrect dates. For example, enter an end date which is before the start
date. I have managed to validate just start date against end date but
when
it comes to a formula to include all the columns I get lost.
PS
The entry in the cell must be greater than 01/01/2007 and today

custom data validation
 

"Bob Phillips" wrote:

Select L2:On and in DV use a formula of

=AND(L2$K2,L2<P2)

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Gareth" wrote in message
...
I have a sheet with 6 columns of dates (K to P). K is the job start date,
P
is the job end date and the ones inbetween are inbetween dates. It is
possible for some of the ones inbetween not to need to have a date in them
(left blank).
What I want to do is validate the cells so that the user cannot enter
incorrect dates. For example, enter an end date which is before the start
date. I have managed to validate just start date against end date but
when
it comes to a formula to include all the columns I get lost.
PS
The entry in the cell must be greater than 01/01/2007 and today



Bob

I don't think that I have explained exactly what I need...

All cells in the columns K:P need dv, each cell cannot be less than the cell
to its left. For example:
K2 cannot be greater than L2, M2, N2, O2 or P2
L2 cannot be less than K2 or greater than M2, N2, O2 or P2
M2 can be blank, cannot be less than K2 or L2 or greater than N2, O2 or P2 -
etc

M2, N2 and O2 can be blank.

Dates entred must be =01/01/2007

Thanks in advance.

Gareth