+ Reply to Thread
Results 1 to 8 of 8

Need to find lowest date after today's date

  1. #1
    Registered User
    Join Date
    09-20-2010
    Location
    California
    MS-Off Ver
    Excel 2007
    Posts
    4

    Need to find lowest date after today's date

    I have series of dates in a row (with some blank cells) and want to return the lowest value, AFTER today's date

    Using the MIN function, I can find the lowest date in the row
    Using the NOW() function, I can find today's date

    I can't figure out how to use either the AND function or an IF statement to return the lowest value after today's date.

    Any help is greatly appreciated.

  2. #2
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676

    Re: Need to find lowest date after today's date

    You can use an array formula like this

    =MIN(IF(A2:Z2>TODAY(),A2:Z2))

    confirmed with CTRL+SHIFT+ENTER
    Audere est facere

  3. #3
    Registered User
    Join Date
    09-20-2010
    Location
    California
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: Need to find lowest date after today's date

    Thanks for the help!

    When I tried that I get the return of 1/0/1900. I've gotten this result a couple different ways and I am not sure how to fix it.

    Thoughts?

    I did press CTRL+SHIFT+ENTER after entering the formula
    Last edited by jerpenny; 09-21-2010 at 12:19 PM.

  4. #4
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,569

    Re: Need to find lowest date after today's date

    If you did it properly, you'll see brackets around your formula { } Is that the case?
    ChemistB
    My 2

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  5. #5
    Registered User
    Join Date
    09-20-2010
    Location
    California
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: Need to find lowest date after today's date

    Yes I see the { }

    The code reads {=MIN(IF(B19:G19>TODAY(),B19:G19))}

  6. #6
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,569

    Re: Need to find lowest date after today's date

    That should only return 1/0/1900 if there are no values in B19:G19 > TODAY() Are you sure that your dates are recorded as numbers and not text?

  7. #7
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,404

    Re: Need to find lowest date after today's date

    Mayve this?

    =SMALL(A1:A24,1+COUNTIF(A1:A24;"<"&TODAY()))
    Last edited by zbor; 09-21-2010 at 02:05 PM.

  8. #8
    Registered User
    Join Date
    09-20-2010
    Location
    California
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: Need to find lowest date after today's date

    They were indeed formatted as dates. However, I copied and pasted the exact same formula and it worked the second time. Don't know what I did differently.

    Thank you very much for your help!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

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