# If statement between dates

1. ## If statement between dates

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.

2. ## Re: If statement between dates

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.

3. ## Re: If statement between dates

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")

4. ## Re: If statement between dates

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

6. ## Re: If statement between dates

Yes that is correct is the rg in column a starts with a number it should also be not applicable

7. ## Re: If statement between dates

Hi,

Try

Formula:
`Please Login or Register  to view this content.`

8. ## Re: If statement between dates

Richard,
The code is only working sometimes, Please see my example attached.
Thanks
Johnny

9. ## Re: If statement between dates

Originally Posted by Burt_100
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.

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.

10. ## Re: If statement between dates

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)

11. ## Re: If statement between dates

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

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1