Data Validation for a date

M

mgkaam

I have a column that people will enter any date in this format 01/01/07, I
need to validate that if this date is older than 60 days from Today's date
than it will give me an error. What kind of formula would I need to achieve
this? I have to do this for 50 rows in the same column.

Thanks,
 
S

Sara

Hi,

IF columm A is the cell to enter date. Then enter this formula in columm B,
next to the cell which you enter the date. Lets say if you you have entered
date in cell A1, then enter this formula in cell B1.

=IF(A1-TODAY()>=60,"ERROR","")

If the date minus today is more or equal to 60, then the words 'ERROR' will
appear in cell B1. After enter this formula, just drag down to whichever row
required.

Hope it helps.
Sara
 
M

mgkaam

Is there a way to actually put the formula in the Data> Validation>Settings
area for the 50 rows which are am1:am50. I would rather do it there than in
a cell. I have tried it in there but I don't know what to put the Data
Settings i.e. between, equal to, not equal too..... I know I am asking for a
lot but this file is than saved as a .txt file and I don't want to add any
more columns for just this formula.

Thanks again for the fast response
 
R

Roger Govier

Hi

Mark cells AM1:AM50.
Data>Validation>Custom>Formula> =A1-TODAY()>=60
Select Error Alert tab and type in suitable message
 
M

mgkaam

I tried that but than I get the error for whatever date I put in, let me
explain a little more, this field is a field for signatures dates off a form
we receive, so it can have any date in there up to 60 days old from today's
date, but like i said before this spreadsheet already has columns with other
information in them that are hidden and I can't add another column that is
why i am trying to put it in the data validation part. I get my error I put
in but it is for everything, do I need to format the cell a certain way? It
does need to be in mm/dd/yyyy format. Is there a macro i could right that
could check the form before I save it to a text file that would than give me
an error so that I can fix this? Sorry I am being complicated but I really
do appreciate an help I can get!!!!!!
 
M

mgkaam

Sorry I have been out for a few days. I tried =AM1-TODAY()>=60 in the data
validation field for that cell but the problem is that I can enter in any
date in this field and it will take it. What I am looking for is when a date
is entered in this field (01/01/01) it will validate to make sure that date
is not 60 days older and also not past today's date, if today's date is
(01/19/07) than you can't enter in (01/20/07). If it is than it will return
the alert "the date is 60 days old". If I can do it as a macro to where it
looks in all of my 50 columns (AM1:AM50) and returns that error each time a
date is entered that is older than today. My cells are formated as
custom>date mm/dd/yyyy is this correct? I tried putting the =am1-today()=60
in the cell itself but it just returns the 60days from today in that cell, I
just need it to validate. Sorry for the long question.
 
Top