Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Date data not viewed as Date? | Excel Discussion (Misc queries) | |||
Mileage Claim Formula | New Users to Excel | |||
Inputting data to one worksheet for it effect another | Excel Discussion (Misc queries) | |||
ranking query | Excel Discussion (Misc queries) | |||
Sort pages? | Excel Discussion (Misc queries) |