+ Reply to Thread
Results 1 to 7 of 7

Earliest or latest date based on variable criteria

  1. #1
    Valued Forum Contributor
    Join Date
    12-02-2009
    Location
    Austin, Tx
    MS-Off Ver
    Office 365 64-Bit, 2108, build 14326.21018
    Posts
    3,946

    Earliest or latest date based on variable criteria

    I've posted an example. Ignore the errors in A through E; it's the Yellow errors I'm looking for help with.

    Basically, people list a description of the Requisitions they do. Descriptions are like "Translations, Invoice xxx", "Translation Invoice xxx", "DayCare-68P","Daycare-63P", "DayCare Service", etc. I need a formula to return the first date of Daycare, no matter how the Description reads, or the first date of translation. I thought my Aggregate formula would get it but it's not working the way I want. I can't do a dropdown to choose because, for instance, Daycare has 200 different service codes that could be used with it, and the whole "Descr" field is supposed to be just a way to keep track of pending stuff, but my boss want's to gather statistics based on it.
    I know I'm not stupid, but I suspect I'm a lot stupider than I think I am

  2. #2
    Forum Expert XLent's Avatar
    Join Date
    10-13-2010
    Location
    Northumberland, UK
    MS-Off Ver
    various
    Posts
    2,704

    Re: Earliest or latest date based on variable criteria

    You will have issues given your DNR definitions (COUNTA) and I'd advise against using OFFSET as dependents become volatile by association; so, for ex.

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    note use of Col A to determine Row - i.e. pick a column that is always populated and use that for each definition - thereby ensuring identical dimensions for each DNR.
    repeat for each name
    alternatively, convert to a Table and use Structured References.

    in terms of your MIN date requirement, assuming you've fixed inconsistencies in your DNR definitions...

    not entirely sure I follow the requirement re: search criteria but, you need to account for blanks (with 15)

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Last edited by XLent; 05-15-2020 at 10:16 AM. Reason: put in the N(+ just in case...

  3. #3
    Valued Forum Contributor
    Join Date
    12-02-2009
    Location
    Austin, Tx
    MS-Off Ver
    Office 365 64-Bit, 2108, build 14326.21018
    Posts
    3,946

    Re: Earliest or latest date based on variable criteria

    XLent,
    Great solutions! I'm working on plumbing them through my entire workbook, and will update once I've completed.

  4. #4
    Valued Forum Contributor
    Join Date
    12-02-2009
    Location
    Austin, Tx
    MS-Off Ver
    Office 365 64-Bit, 2108, build 14326.21018
    Posts
    3,946

    Re: Earliest or latest date based on variable criteria

    Okay, plumbing done, and it's working flawlessly. Thanks for all the help!

    I have a question; I originally went with AGGREGATE because we were on Office 2013, but we recently got upgraded to 2016; would MINIFS be able to substitute for AGGREGATE in this situation? I tried to go that route but couldn't figure out how to do it.

  5. #5
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Earliest or latest date based on variable criteria

    MINIFS is not available in Excel 2016. It’s in Excel 2019 and the 365 subscription version.

    https://support.office.com/en-gb/art...c-72eef32e6599
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  6. #6
    Valued Forum Contributor
    Join Date
    12-02-2009
    Location
    Austin, Tx
    MS-Off Ver
    Office 365 64-Bit, 2108, build 14326.21018
    Posts
    3,946

    Re: Earliest or latest date based on variable criteria

    I have "Microsoft Excel for Office 365 MSO (16.0.10730.20344) 64-Bit" and DO have MINIFS.

  7. #7
    Forum Expert XLent's Avatar
    Join Date
    10-13-2010
    Location
    Northumberland, UK
    MS-Off Ver
    various
    Posts
    2,704

    Re: Earliest or latest date based on variable criteria

    so, yes, you could use MINIFS if happy to accept backwards compatibility limitations with 2016 -- something like below could replace your AGGREGATE

    =MINIFS(Date_to_AP, Date_to_AP,">0",Desc,"*Translation*")

+ 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] 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
  2. 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
  3. Find earliest date before today based on criteria
    By DanNielsen in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 02-28-2018, 10:47 AM
  4. Replies: 3
    Last Post: 02-10-2016, 07:26 PM
  5. Replies: 2
    Last Post: 03-20-2015, 01:55 AM
  6. Replies: 3
    Last Post: 08-31-2014, 06:39 AM
  7. Find earliest date based on certain criteria
    By SeanKosmann in forum Excel Formulas & Functions
    Replies: 16
    Last Post: 01-26-2010, 11:32 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