Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 6
Default Data Validation for a date

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,
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 82
Default Data Validation for a date

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


"mgkaam" wrote:

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,

  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 6
Default Data Validation for a date

Is there a way to actually put the formula in the Data ValidationSettings
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

"Sara" wrote:

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


"mgkaam" wrote:

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,

  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2,886
Default Data Validation for a date

Hi

Mark cells AM1:AM50.
DataValidationCustomFormula =A1-TODAY()=60
Select Error Alert tab and type in suitable message

--
Regards

Roger Govier


"mgkaam" wrote in message
...
Is there a way to actually put the formula in the Data
ValidationSettings
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

"Sara" wrote:

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


"mgkaam" wrote:

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,



  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 6
Default Data Validation for a date

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!!!!!!

"Roger Govier" wrote:

Hi

Mark cells AM1:AM50.
DataValidationCustomFormula =A1-TODAY()=60
Select Error Alert tab and type in suitable message

--
Regards

Roger Govier


"mgkaam" wrote in message
...
Is there a way to actually put the formula in the Data
ValidationSettings
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

"Sara" wrote:

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


"mgkaam" wrote:

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,






  #6   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2,886
Default Data Validation for a date

Hi

My apologies
=A1-TODAY()=60

should of course have been

=AM1-TODAY()=60

--
Regards

Roger Govier


"mgkaam" wrote in message
...
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!!!!!!

"Roger Govier" wrote:

Hi

Mark cells AM1:AM50.
DataValidationCustomFormula =A1-TODAY()=60
Select Error Alert tab and type in suitable message

--
Regards

Roger Govier


"mgkaam" wrote in message
...
Is there a way to actually put the formula in the Data
ValidationSettings
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

"Sara" wrote:

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


"mgkaam" wrote:

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,






  #7   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 6
Default Data Validation for a date

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


"mgkaam" wrote:

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,

  #8   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2,886
Default Data Validation for a date

Hi

Try
=AND(AM1<=TODAY(),TODAY()-AM1<=60)

--
Regards

Roger Govier


"mgkaam" wrote in message
...
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
customdate 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.


"mgkaam" wrote:

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,



  #9   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 6
Default Data Validation for a date

Thank you so much that worked perfectly!

"mgkaam" wrote:

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,

  #10   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2,886
Default Data Validation for a date

Hi

Glad we got there in the end. Thanks for the feedback.

--
Regards

Roger Govier


"mgkaam" wrote in message
...
Thank you so much that worked perfectly!

"mgkaam" wrote:

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,



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Date data not viewed as Date? Jugglertwo Excel Discussion (Misc queries) 1 December 21st 06 08:05 PM
Mileage Claim Formula johndavies New Users to Excel 4 August 14th 06 09:24 AM
Inputting data to one worksheet for it effect another daedalus1 Excel Discussion (Misc queries) 1 June 25th 06 04:39 PM
ranking query JaimeTimbrell Excel Discussion (Misc queries) 2 February 16th 06 09:09 AM
Sort pages? David Excel Discussion (Misc queries) 15 May 13th 05 11:33 PM


All times are GMT +1. The time now is 01:06 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"