Can anyone tell me why this if statement is only working sometimes?
I want to check that the date is before 01/06/2010 but no older than 10 years, Here is what I have;
=IF(AND(A2<DATEVALUE("01/06/2010"),A2>=TODAY()-3652),"Applicable", "Not Applicable")
I have attached a sample workbook showing the outcomes, I also need it to show "Applicable" when the date exceeds the 5 years - in simple terms once 01/06/2015 has been reached and the date entered is on or before this date.
Last edited by Burt_100; 04-05-2014 at 09:30 AM.
HI,
There seems a contradiction. Your first statement wants to check that the date is not older than 10 years but then you mention 5 years in the seconds para.
Would you manually add the results you expect for a selection of dates that cover all permutations along with a note for each one explaining the result.
Richard Buttrey
RIP - d. 06/10/2022
If any of the responses have helped then please consider rating them by clicking the small star iconbelow the post.
I cannot test this, because your dates are in Euro format, but if it is failing on the " / / " entries, test with this...
=IF(ISNUMBER(A2),IF(AND(A2<DATEVALUE("01/06/2010"),A2>=TODAY()-3652),"Applicable", "Not Applicable"),"No date")
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
Hi,
I have added a more explanatory workbook, Dont worry about the over 5 years things when it comes along next year I can modify the formula, I have now added that the rg must start with a minimum of 1 letter so the formula will go something like if A2 starts with letter & B2 is after 01-06-2010 and less than 10 years result is applicable else result is not applicable.
Hi
Please clarify. A8 and A11 both start with with a 'number' i.e. 0 & 6. Yet if I read your requirement correctly, because they don't start with a letter they should be Not Applicable.
Yes that is correct is the rg in column a starts with a number it should also be not applicable
Hi,
Try
Formula:
Please Login or Register to view this content.
Richard,
The code is only working sometimes, Please see my example attached.
Thanks
Johnny
Originally Posted by Burt_100
Hi,
Avoid typing information that may change at any time in your formulas, better use a cell name that refers to it.
A1 = 01/06/2010
A2 = Date to tested
B1 = 10 (Number of years)
Try to use this formula:
=IF(IF(ISNUMBER(A2),IF(VALUE(A2)<VALUE($A$1),1,0),0)=0,"Not Applicable",IF(VALUE(A2)>=TODAY()-365.25*$B$1,"Applicable","Not Applicable"))
I think this should work in any case, let me know.
shorter formula:
IF(VALUE(A2)>=VALUE($A$1),"Not Applicable",IF(VALUE(A2)>=TODAY()-365.25*$B$1,"Applicable","Not Applicable"))
with:
A1 = "01/06/2010"
A2 = "Date to tested"
B1 = "10" (Number of years)
shorter formula:
IF(VALUE(A2)>=VALUE($A$1),"Not Applicable",IF(VALUE(A2)>=TODAY()-365.25*$B$1,"Applicable","Not Applicable"))
with:
A1 = "01/06/2010"
A2 = "Date to tested"
B1 = "10" (Number of years)
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks