# Need to find lowest date after today's date

1. ## 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. ## 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

3. ## 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

4. ## 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?

5. ## Re: Need to find lowest date after today's date

Yes I see the { }

6. ## 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. ## Re: Need to find lowest date after today's date

Mayve this?

=SMALL(A1:A24,1+COUNTIF(A1:A24;"<"&TODAY()))

8. ## 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!

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

#### 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