+ Reply to Thread
Results 1 to 6 of 6

If, And, Or within a Date Range???

  1. #1
    Registered User
    Join Date
    01-08-2013
    Location
    Chicago, IL
    MS-Off Ver
    Excel 2010
    Posts
    9

    If, And, Or within a Date Range???

    Hello Forum,

    I was hoping someone could help me out. I'm looking for a formula that can look at three fields in date format. From there it needs to be able to identify:

    1. All 3 parameters have been completed (date filled in the box)
    or
    2. At least 1 of the parameters (dates) is within the past year

    Oh, and once the formula has identified the answer, a simple yes/no to populate in the box would be great.


    I've attached an image with an example... Forum Question.JPG


    Thanks everyone!

  2. #2
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,147

    Re: If, And, Or within a Date Range???

    hi rstarr1, welcome to the forum. try this in E2:
    =IF(OR(COUNT(B2:D2)=3,SUMPRODUCT(--(YEAR(B2:D2)=YEAR(TODAY())-1))),"yes","no")

    do consider uploading a sample Excel file next time in the thread as it will enable us to help you better. to upload, press "Go Advanced" beside the "Post Quick Reply" button & click on the paperclip icon
    ideally, it should contain your desired results

    Thanks, if you have clicked on the * and added our rep.

    If you're satisfied with the answer, click Thread Tools above your first post, select "Mark your thread as Solved".

    "Contentment is not the fulfillment of what you want, but the realization of what you already have."


    Tips & Tutorials I Compiled | How to Get Quick & Good Answers

  3. #3
    Forum Expert Tsjallie's Avatar
    Join Date
    09-15-2012
    Location
    NL
    MS-Off Ver
    2010, 2013, 2016
    Posts
    2,077

    Re: If, And, Or within a Date Range???

    Or this:
    Please Login or Register  to view this content.
    Notice the brackets around the formula. So don't forget to finish the formula hitting [ctrl]+[enter]
    Cheers!
    Tsjallie




    --------
    If your problem is solved, pls mark the thread SOLVED (see Thread Tools in the menu above). Thank you!

    If you think design is an expensive waste of time, try doing without ...

  4. #4
    Registered User
    Join Date
    01-08-2013
    Location
    Chicago, IL
    MS-Off Ver
    Excel 2010
    Posts
    9

    Re: If, And, Or within a Date Range???

    Wow! You're both awesome!

    Can I add one more piece? I think I mistyped... instead of within the past year, how could this be written within a year from a specific date... such as 12/31/2012 for the example?


    Thanks!

  5. #5
    Forum Expert Tsjallie's Avatar
    Join Date
    09-15-2012
    Location
    NL
    MS-Off Ver
    2010, 2013, 2016
    Posts
    2,077

    Re: If, And, Or within a Date Range???

    You can alter the formula as follows:
    Please Login or Register  to view this content.
    Where somedate can be a literal or a reference.
    And do notice the extra OR-construct. Skipped that one in my previous post.

  6. #6
    Registered User
    Join Date
    01-08-2013
    Location
    Chicago, IL
    MS-Off Ver
    Excel 2010
    Posts
    9

    Re: If, And, Or within a Date Range???

    Hey Benishiryo... from your formula, how could this be written within a year from a specific date... such as 12/31/2012 for the example?



    Thanks.

  7. #7
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,147

    Re: If, And, Or within a Date Range???

    you're welcome. is it within that particular year of that date mentioned (i.e. 2012) or is at within 1 year prior to that date? 31st Dec 2012 not a good eg. what if your date is 30 sep 2012. is it anything that is year 2012 or is it 1 Oct 2011 to 30 Sep 2012?
    for the 1st, it'll be:
    =IF(OR(COUNT(B2:D2)=3,SUMPRODUCT(--(YEAR(B2:D2)=YEAR("31dec12")))),"yes","no")

    for the 2nd:
    =IF(OR(COUNT(B2:D2)=3,SUMPRODUCT((B2:D2<=--("30sep12"))*(B2:D2>EDATE(--("30sep12"),-12)))),"yes","no")

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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