+ Reply to Thread
Results 1 to 4 of 4

Return the latest date based on the earliest date

  1. #1
    Registered User
    Join Date
    01-13-2018
    Location
    Ethiopia
    MS-Off Ver
    2013
    Posts
    27

    Return the latest date based on the earliest date

    Hi Everyone,

    I have two tables one for customer shipment and the other for customer return.

    The "customer shipment" table contains dates when a customer buys a reusable product and the "customer return" table contains dates that customer returns.

    What i want to be able to do is determine the return date of the customer using dates "customer shipment" table.

    For example if a customer took product on lets say 1/1/2021 and 1/12/2021 and in the customer return table i have this customer's return dates of

    12/25/2020, 12/31/2020, 1/7/2021 and 1/20/2021.

    i want to bring in the "shipment table" the return date of this customer so for the above example,

    customer A took product in 1/1/2021 so in the "customer return table", search the latest date next to 1/1/2021 which would be 1/7/2021 and bring all information on that date.

    Again

    Customer A took product in 1/12/2021 so in the "customer return table", search the latest return date next to 1/12/2021 which would be 1/20/2021 and bring all information on that date.

    I have attached a sample excel

    Customer return and Shipment Matrix.xlsx

    Thanks in advance for your reply

  2. #2
    Valued Forum Contributor
    Join Date
    09-30-2018
    Location
    Vlaams Brabant Belgium
    MS-Off Ver
    365
    Posts
    361

    Re: Return the latest date based on the earliest date

    Hi,

    You use the new formulas

    return date
    =INDEX(FILTER(Table2;([@[Customer Name]]=Table2[Customer Name])*(Table2[Return Date] >= [@[Shipment Date]]));1;{1})

    Doc.No
    =INDEX(FILTER(Table2;([@[Customer Name]]=Table2[Customer Name])*(Table2[Return Date] >= [@[Shipment Date]]));1;{3})

    But these are for the new 365 or 2019 excel version
    Please be as complete as possible in your asking so it may save use all the time to rework the solution because you didn't give all the requirements. If you have a layout in mind please work it out first so we can adapt our solution to it. Thanks.
    If you have been helped, maybe you could click the *

  3. #3
    Registered User
    Join Date
    01-13-2018
    Location
    Ethiopia
    MS-Off Ver
    2013
    Posts
    27

    Re: Return the latest date based on the earliest date

    Thank you for your reply but unfortunately i am only using the 2016 version of excel.

  4. #4
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    12,082

    Re: Return the latest date based on the earliest date

    Please update the profile to indicate that the "MS-off Ver:" is 2016
    The following formulas should work with the 2016 version of Excel:
    1. For return date:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    2. For doc no. of return date :
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Let us know if you have any questions.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

+ 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. Earliest or latest date based on variable criteria
    By jomili in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 05-18-2020, 09:35 AM
  2. [SOLVED] Return value based on earliest Date and Criteria
    By alexandrabiorka in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-13-2020, 10:51 PM
  3. Find earliest and latest time based on specific date
    By klturi421 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-05-2019, 11:20 PM
  4. Replies: 3
    Last Post: 02-10-2016, 07:26 PM
  5. Replies: 1
    Last Post: 11-25-2015, 03:31 PM
  6. Replies: 14
    Last Post: 04-09-2015, 12:43 PM
  7. Replies: 3
    Last Post: 08-31-2014, 06:39 AM

Tags for this Thread

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