+ Reply to Thread
Results 1 to 3 of 3

Oldest and Newest Date based on 2 Criteria

  1. #1
    Forum Contributor
    Join Date
    08-15-2014
    Location
    Michigan, USA
    MS-Off Ver
    MS Office 2021
    Posts
    105

    Oldest and Newest Date based on 2 Criteria

    In Column A I have a date I did work for a customer.
    In Column B I have the customer Area.
    In Column C I have the customer #.

    Now in Column E & F I have the Area & customer #.
    In Column G I want to find the oldest date based on the customer # in the Area.
    In Column H I want to find the newest date based on the customer # in the Area.

    Some times the oldest date and newest date will match if I only worked for the customer once.

    I know I can sort, to figure this out, but these figure are in different books.
    I need to find these dates without opening multi books and sorting and retyping dates.
    Attached Files Attached Files

  2. #2
    Forum Expert leelnich's Avatar
    Join Date
    03-20-2017
    Location
    Delaware, USA
    MS-Off Ver
    Office 2016
    Posts
    2,807

    Re: Oldest and Newest Date based on 2 Criteria

    Hi MRozell- Try these ARRAY FORMULAE* :
    Oldest:
    =MIN(IF(($B$2:$B$109=$E2)*($C$2:$C$109=$F2),$A$2:$A$109))

    Newest:
    =MAX(IF(($B$2:$B$109=$E2)*($C$2:$C$109=$F2),$A$2:$A$109))

    *Always press CTRL+SHIFT+ENTER to confirm entry after pasting or editing an ARRAY FORMULA in the Formula Bar.
    When copying the formula to other cells, make sure the copied cell is not part of the Paste Area.


    Please click the Add Reputation star below any helpful posts, and if you have your answer, mark your thread as SOLVED (Thread Tools up top). Thanks!-Lee
    Attached Files Attached Files
    Last edited by leelnich; 07-21-2017 at 12:17 PM.

  3. #3
    Forum Contributor
    Join Date
    07-28-2012
    Location
    USA
    MS-Off Ver
    Excel 2007
    Posts
    104

    Re: Oldest and Newest Date based on 2 Criteria

    Another option is using database functions, but you will need a helper worksheet. Unfortunately, database functions do not work with discontinuous ranges.

+ 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] Grouping cell with blank cells below and sort by date oldest to newest
    By Lucille Boshoff in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 07-14-2017, 10:28 AM
  2. [SOLVED] Vba code to lookup newest and oldest date
    By UPA in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 05-01-2017, 07:27 AM
  3. Arranging Pivot table data from Newest to oldest to Newest does not appear
    By paradise2sr in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 05-08-2014, 06:53 AM
  4. [SOLVED] lesson grades (from oldest date to newest date)
    By aaaaa34 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 02-04-2014, 08:40 AM
  5. sorting a selected range by date, oldest to newest
    By zed commander in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 07-19-2012, 12:40 PM
  6. Display value based on oldest date and other criteria.
    By Permagrin in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-03-2009, 09:48 AM
  7. Lookup Adjacent Cell Values based on Newest and Oldest Date
    By nevi in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-09-2008, 02:27 PM

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