+ Reply to Thread
Results 1 to 10 of 10

Trouble creating formula to find lowest date

  1. #1
    Registered User
    Join Date
    09-09-2020
    Location
    Ohio, USA
    MS-Off Ver
    Version 1908
    Posts
    22

    Trouble creating formula to find lowest date

    I am having trouble creating a formula for these dates. My goal is to create something that will compare all the dates listed. It should do so by taking dates 1-5 and adding 5 years to them, and adding 6 months to "date 6 months" then it should display the lowest of these values in expiration date. It has been giving me a lot of issues and I'm also not sure how to make it so it ignores cells that are empty, as excel assumes the date is 1900 when the cell is blank.
    Attached Files Attached Files
    Last edited by DaBestEva; 11-20-2020 at 04:48 PM.

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    47,489

    Re: Trouble creating formula to find lowest date

    Try this:

    =MIN(EDATE(MIN(A6:E6),60),EDATE(F6,6))
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  3. #3
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 2016 (Windows)
    Posts
    4,973

    Re: Trouble creating formula to find lowest date

    In G6, array-enter (enter using Ctrl-Shift-Enter) the formula

    =MIN(IF(A6:E6<>"",DATE(YEAR(A6:E6)+5,MONTH(A6:E6),DAY(A6:E6))),IF(F6>0,EDATE(F6,6)))

    or this, suggested by Ali's post

    =MIN(EDATE(MIN(IF(A6:E6<>"",A6:E6)),60),IF(F6>0,EDATE(F6,6)))
    Last edited by Bernie Deitrick; 11-20-2020 at 01:05 PM.
    Bernie Deitrick
    Excel MVP 2000-2010

  4. #4
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 2016 (Windows)
    Posts
    4,973

    Re: Trouble creating formula to find lowest date

    That's a smart usage of EDATE. I tried it with the 5 cells and it didn't like that... but if one is blank.....

  5. #5
    Registered User
    Join Date
    09-09-2020
    Location
    Ohio, USA
    MS-Off Ver
    Version 1908
    Posts
    22

    Re: Trouble creating formula to find lowest date

    I tried implementing the formula, but I guess I tried to dumb down my setup too much when I posted initially. Can you try doing the same for this new setup?
    Attached Files Attached Files

  6. #6
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 2016 (Windows)
    Posts
    4,973

    Re: Trouble creating formula to find lowest date

    In N5, array-enter with Ctrl-Shift-Enter

    =MIN(EDATE(MIN(IF(ISNUMBER(B5:L5),B5:L5)),60),IF(ISNUMBER(M5),EDATE(M5,6)))

  7. #7
    Registered User
    Join Date
    09-09-2020
    Location
    Ohio, USA
    MS-Off Ver
    Version 1908
    Posts
    22

    Re: Trouble creating formula to find lowest date

    When the Date + 6 months is blank, the expiration date defaults to 1/0/1900, any idea how to fix that?

  8. #8
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 2016 (Windows)
    Posts
    4,973

    Re: Trouble creating formula to find lowest date

    I didn't try that one - I thought the others might be blanks...

    Anyway, array-enter this - if you have no dates at all it will return a blank, and it should handle various missing dates better:

    =IF(COUNT(B5:M5)=0,"",MIN(IF(COUNT(B5:L5)=0,100000,EDATE(MIN(IF(ISNUMBER(B5:L5),B5:L5)),60)),IF(M5>0,EDATE(M5,6),100000)))

  9. #9
    Registered User
    Join Date
    09-09-2020
    Location
    Ohio, USA
    MS-Off Ver
    Version 1908
    Posts
    22

    Re: Trouble creating formula to find lowest date

    Thanks, Bernie! This works wonders, I can't tell you how long I tried messing around with it!

  10. #10
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    47,489

    Re: Trouble creating formula to find lowest date

    Well done, Bernie - and thank you for the compliment. It made contributing to the thread worthwhile.

+ 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: 3
    Last Post: 06-13-2020, 10:28 PM
  2. Find Lowest Value between 6 months of a date
    By bagitsellit in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 05-29-2019, 12:44 PM
  3. [SOLVED] Stuck creating formula that sorts highest to lowest then sum quantity
    By ZMAFC94 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 08-30-2018, 10:06 AM
  4. Replies: 0
    Last Post: 03-27-2014, 03:55 PM
  5. Replies: 4
    Last Post: 07-06-2013, 04:44 PM
  6. [SOLVED] Find the lowest date value from WS names
    By jordan2322 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-15-2012, 07:13 PM
  7. Need to find lowest date after today's date
    By jerpenny in forum Excel General
    Replies: 7
    Last Post: 09-21-2010, 02:02 PM

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