+ Reply to Thread
Results 1 to 11 of 11

How do I calculate closest past & closest future dates base on multiple criteria

  1. #1
    Registered User
    Join Date
    07-20-2017
    Location
    Birmingham, UK
    MS-Off Ver
    Office 365
    Posts
    10

    How do I calculate closest past & closest future dates base on multiple criteria

    Hi,

    Please see attached file.

    This is a table made up of a list of Part Numbers (column A) and a corresponding delivery date (column B). In column C I have entered the following formula (after research online and in this forum):

    =INDEX($B$4:$B$778,MATCH(MIN(IF($A$4:$A$778=$A4,ABS($B$4:$B$778-$B$1))),IF($A$4:$A$778=$A4,ABS($B$4:$B$778-$B$1)),0))

    I also use CTRL+SHIFT+ENTER when completing

    I know my way around excel a little, but am certainly no guru. It is my belief that this formula is providing me with the closest delivery date prior to the date I have specified in cell B1. However for some reason a few dates seem to produce #VALUE! but I am unsure why?
    I am not too fussed at this point about the results in column C where there is no delivery date supplied in column B

    I have 2 main queries that I am seeking your help with:

    (1) Is the formula in column C giving me the result I think it is (closest past date)? And if so, is it structured correctly or is it over complicated?

    (2) How can this formula be amended to provide me with the closest future date (to be entered separately in column D for example)

    Any help or feedback would be greatly appreciated!

    Many thanks
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    HOME: Excel 2007 - WORK: Excel 2013
    Posts
    7,007

    Re: How do I calculate closest past & closest future dates base on multiple criteria

    Are you sure this formula works?

    The first part number NP50232 has delivery of 21/04.
    The same part number occurs twice more in the list with dates of 9/5 and 4/7
    4/7 is the value returned by your formula in all cases, but 4/7 is NOT prior to 21/4 ???


    What happens if there si no prior date, what result do you want then?
    Regards
    Special-K

    Ensure you describe your problem clearly, I have little time available to solve these problems and do not appreciate numerous changes to them.

  3. #3
    Registered User
    Join Date
    07-20-2017
    Location
    Birmingham, UK
    MS-Off Ver
    Office 365
    Posts
    10

    Re: How do I calculate closest past & closest future dates base on multiple criteria

    I can see now that this formula is not actually doing what I wanted it to.

    In all cases, the date I want to return last prior date and next future date is against cell B2 (16/06/2017). However the value being returned against Part Number NP50232 (value 04/07/2017) is clearly not prior to 16/06/2017!

    If there is no prior or future date, just returning a blank cell would be fine in this particular case.

  4. #4
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    HOME: Excel 2007 - WORK: Excel 2013
    Posts
    7,007

    Re: How do I calculate closest past & closest future dates base on multiple criteria

    Try

    For most recent past date
    =IF(MAX(IF((A$4:A$778=A4)*(B$4:B$778<B4),B$4:B$778))=0,"",MAX(IF((A$4:A$778=A4)*(B$4:B$778<B4),B$4:B$778)))
    Array formula, use Ctrl-Shift-Enter

    For next future date
    =IF(MIN(IF((A$4:A$778=A4)*(B$4:B$778>B4),B$4:B$778))=0,"",MAX(IF((A$4:A$778=A4)*(B$4:B$778>B4),B$4:B$778)))
    Array formula, use Ctrl-Shift-Enter

    and copy down the columns

  5. #5
    Registered User
    Join Date
    07-20-2017
    Location
    Birmingham, UK
    MS-Off Ver
    Office 365
    Posts
    10

    Re: How do I calculate closest past & closest future dates base on multiple criteria

    This is great, exactly what I was after!

    One additional query - I have now added the Delivery Qty in column C (see attached updated file "Book1.2.xls") which I would like to link to each part number and respective recent/future delivery date. I thought one way of doing this could be to concatenate Part Number & Date and do some kind of lookup, however on occasion there are Part Numbers that have more than one delivery on the same date, plus a lookup would only pickup the first record it matches with.

    Is there a way to pull the Qty across from column C and match it to the relevant most recent and next future dates, as calculated in your formula solution below?

    I suspect maybe some kind of index/match is required.
    Attached Files Attached Files

  6. #6
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    HOME: Excel 2007 - WORK: Excel 2013
    Posts
    7,007

    Re: How do I calculate closest past & closest future dates base on multiple criteria

    Does this work? Check carefully the part numbers with more than one delivery on the same date, I think it's only going to pull the first one.

    in E4
    =IF(D4="","",VLOOKUP(A4,IF((B$4:B$778=D4),A$4:C$778),3,0))
    Array formula, use Ctrl-Shift-Enter

    in G4
    =IF(F4="","",VLOOKUP(A4,IF((B$4:B$778=F4),A$4:C$778),3,0))
    Array formula, use Ctrl-Shift-Enter

  7. #7
    Registered User
    Join Date
    07-20-2017
    Location
    Birmingham, UK
    MS-Off Ver
    Office 365
    Posts
    10

    Re: How do I calculate closest past & closest future dates base on multiple criteria

    Hi, yes it only pulls the first value.
    For example, Part Number RPUKSN10591 has 2 deliveries on 22/06 for two different quantities (17,430 & 6,608), however that formula picks up the 17,430 figure for both records.

  8. #8
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    HOME: Excel 2007 - WORK: Excel 2013
    Posts
    7,007

    Re: How do I calculate closest past & closest future dates base on multiple criteria

    If you have a duplicate part number with multiple deliveries on the same date
    how do you know which value you want to return, ie whats the rule to determine which value to return?

  9. #9
    Registered User
    Join Date
    07-20-2017
    Location
    Birmingham, UK
    MS-Off Ver
    Office 365
    Posts
    10

    Re: How do I calculate closest past & closest future dates base on multiple criteria

    Good point! Thinking about it, I'd actually need to SUM the total delivery quantity on the same day for the same part number. So in the example of Part Number RPUKSN10591 I'd want the formula to show 24,038 (17,430 + 6,608).

    It wouldn't matter too much if the 24,038 showed in both records (i.e. the 2 rows for the 2 deliveries on the same day) as if I reference this PNo / Del date / quantity in another sheet or a pivot for example, I could get round that by looking up the first value for each part number of by summarising by MAX or something in a pivot.

  10. #10
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    HOME: Excel 2007 - WORK: Excel 2013
    Posts
    7,007

    Re: How do I calculate closest past & closest future dates base on multiple criteria

    Does this work?

    in E4
    =IF(D4="","",SUMPRODUCT((A$4:A$778=A4)*(B$4:B$778=D4)*(C$4:C$778)))

    in G4
    =IF(F4="","",SUMPRODUCT((A$4:A$778=A4)*(B$4:B$778=F4)*(C$4:C$778)))

  11. #11
    Registered User
    Join Date
    07-20-2017
    Location
    Birmingham, UK
    MS-Off Ver
    Office 365
    Posts
    10

    Re: How do I calculate closest past & closest future dates base on multiple criteria

    I think it does yes! Can't find an example where it doesn't.

    Special-K, thank you for your speedy help with this query today. This element is part of a much larger project and this issue with past/future dates relevant to unique part numbers had proved to be my stumbling block... until now!

    Very much appreciate all your feedback and solutions.

+ 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. [SOLVED] Retrieving the Closest Larger / Closest Smaller Values from a List
    By ChipsSlave in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 03-31-2017, 08:38 AM
  2. Finding closest with multiple criteria
    By anwi12ad in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-04-2015, 06:42 AM
  3. [SOLVED] Multiple criteria (finding closest)
    By jram in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 03-20-2015, 12:02 AM
  4. [SOLVED] Closest match on Multiple Criteria
    By rbetts in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 04-20-2014, 08:33 AM
  5. [SOLVED] get 10 Closest Larger / Closest Smaller Values from a List
    By Auni in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 02-19-2013, 04:07 AM
  6. Finding the closest future date from Today from a list of dates
    By maharg in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-18-2008, 09:45 AM
  7. Replies: 0
    Last Post: 08-25-2005, 02:37 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