+ Reply to Thread
Results 1 to 3 of 3

Help required in comparing two dates and select before date

  1. #1
    Registered User
    Join Date
    03-06-2019
    Location
    Canada
    MS-Off Ver
    Office 365
    Posts
    79

    Help required in comparing two dates and select before date

    Hi friends

    on Cell C2 i want that it should compare the date b2 with i column with match of its PO and which one is before the b2 it display result which is cost corresponding to selected date.
    c2 Should be 10$ as b2 is 01/02/19 so it compare the PO and compare the date "B2" with i2, i3, 14 and select the date which is i2 as it is before b2.

    Thanks in advance
    Attached Images Attached Images
    Attached Files Attached Files

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

    Re: Help required in comparing two dates and select before date

    try this array formula:
    =INDEX($J$2:$J$7,MATCH(A2&"_"&MAX(IF($H$2:$H$7=A2,IF($I$2:$I$7<B2,$I$2:$I$7))),$H$2:$H$7&"_"&$I$2:$I$7,0))

    ...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

    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
    Registered User
    Join Date
    03-06-2019
    Location
    Canada
    MS-Off Ver
    Office 365
    Posts
    79
    This seems max but I want min

    Quote Originally Posted by benishiryo View Post
    try this array formula:
    =INDEX($J$2:$J$7,MATCH(A2&"_"&MAX(IF($H$2:$H$7=A2,IF($I$2:$I$7<B2,$I$2:$I$7))),$H$2:$H$7&"_"&$I$2:$I$7,0))

    ...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

+ 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. Replies: 1
    Last Post: 07-12-2019, 07:22 PM
  2. [SOLVED] Using VBA code to highlight row by comparing 2 dates (if one date is later than the other)
    By Bubbles_A in forum Excel Programming / VBA / Macros
    Replies: 23
    Last Post: 06-05-2018, 12:02 PM
  3. Replies: 0
    Last Post: 05-18-2013, 03:29 AM
  4. Compare expired dates to a required date and sum available inventory
    By emiliekatherine in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-19-2012, 02:02 PM
  5. [SOLVED] Different dates but required of the first date of that month
    By PRADEEPB270 in forum Excel General
    Replies: 2
    Last Post: 09-23-2011, 06:43 AM
  6. Comparing Multiple Dates to Today - Due Date Reminder
    By TheMooCow in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 02-01-2010, 02:08 PM
  7. Comparing dates, but some date fields are blank.
    By dgarris in forum Excel General
    Replies: 1
    Last Post: 02-25-2007, 01:53 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