+ Reply to Thread
Results 1 to 11 of 11

If statement between dates

  1. #1
    Forum Contributor
    Join Date
    06-17-2010
    Location
    Donemana, NI
    MS-Off Ver
    Excel 2007
    Posts
    386

    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.
    Last edited by Burt_100; 04-05-2014 at 09:30 AM.

  2. #2
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    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.
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  3. #3
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,240

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

  4. #4
    Forum Contributor
    Join Date
    06-17-2010
    Location
    Donemana, NI
    MS-Off Ver
    Excel 2007
    Posts
    386

    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.
    Attached Files Attached Files

  5. #5
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: If statement between dates

    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.

  6. #6
    Forum Contributor
    Join Date
    06-17-2010
    Location
    Donemana, NI
    MS-Off Ver
    Excel 2007
    Posts
    386

    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. #7
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: If statement between dates

    Hi,

    Try

    Formula: copy to clipboard
    Please Login or Register  to view this content.

  8. #8
    Forum Contributor
    Join Date
    06-17-2010
    Location
    Donemana, NI
    MS-Off Ver
    Excel 2007
    Posts
    386

    Re: If statement between dates

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

  9. #9
    Registered User
    Join Date
    04-07-2014
    Location
    Port-au-Prince, Haiti
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: If statement between dates

    Quote Originally Posted by Burt_100 View Post
    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. #10
    Registered User
    Join Date
    04-07-2014
    Location
    Port-au-Prince, Haiti
    MS-Off Ver
    Excel 2007
    Posts
    5

    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. #11
    Registered User
    Join Date
    04-07-2014
    Location
    Port-au-Prince, Haiti
    MS-Off Ver
    Excel 2007
    Posts
    5

    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)

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. If statement with Dates
    By ktkreeger in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 07-02-2013, 04:26 PM
  2. IF STATEMENT and Dates
    By gballard in forum Excel General
    Replies: 6
    Last Post: 05-30-2012, 03:31 PM
  3. IF statement and dates
    By jacko311 in forum Excel General
    Replies: 2
    Last Post: 05-15-2009, 10:20 AM
  4. using dates in IF statement
    By bigchuda in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 08-28-2008, 02:55 PM
  5. [SOLVED] If Statement and Dates
    By Toys in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 12-21-2005, 08:35 AM

Bookmarks

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