SOLVED Get next date after Today()

**garveyj** · 06-18-2009, 08:40 AM

Hello - I am trying to write a formula to return the next date after today, but am having some trouble with it. Here is my scenario:

In cell K25 I have a value where if it is greater than 49 I want the forumla to look at the values of Cells L3:L22 (these cells have a range of dates in them). I want the formula to go through these cells and pick the next date after today....If the value in Cell K25 is Less than or equal to 49 I want it to just return Todays date.

I found this post http://www.excelforum.com/excel-gene...ter-today.html which was basically what I was looking for, but for some reason when I put that into my formula it will return the smallest date, even if it is older than today.

So going back to my formula....Here is what I have currently in my cell:
=IF(K25>49,MIN(IF(L3:L22>TODAY(),L3:L22)),TODAY())

Like I mentioned above - this results with dates that are older than today - what can I do to make it return results that are only after todays date?

Thanks for any help you can provide!

**deadlyduck** · 06-18-2009, 08:48 AM

Your formula is an array formula- you will need to enter it into Excel by holding down CTRL+Shift and then click Enter.

That should then work.

**garveyj** · 06-18-2009, 08:54 AM

Sorry, but I do not really understand that - do I click into the cell and then do CTNL+Shift and then enter and then paste the code into the cell?

**garveyj** · 06-18-2009, 08:56 AM

Nevermind - I got it to work

Thanks for your help!

**deadlyduck** · 06-18-2009, 09:03 AM

You're welcome- please ensure that you set your thread title to SOLVED

To mark your thread solved do the following: - Go to the first post - Click edit - Click Advance - Just below the word "Title:" you will see a dropdown with the word No prefix. - Change to Solve - Click Save

**garveyj** · 06-18-2009, 09:10 AM

One more question related to this - can you post text in a cell followed by the result of a formula?

i.e. the next available date is (then the result of the forumla here)?

**daddylonglegs** · 06-18-2009, 09:22 AM

You can use a formula like this:

="The next available date is "&TEXT(IF(K25>49,MIN(IF(L3:L22>TODAY(),L3:L22)),TODAY()),"m/d/yyyy")

confirmed with CTRL+SHIFT+ENTER

Note: if your dates in L3:L22 are in ascending order (i.e. early to late) then you can use a non-array version with LOOKUP, i.e.

="The next available date is "&TEXT(IF(K25>49,LOOKUP(TODAY()+1,L3:L22),TODAY()),"m/d/yyyy")