Calculating Dates From Text

[richardlpalmer]

Goal: I would like a cell to display a "Next" date by comparing text values
in one cell to "today".

Scenario: I have two cells. The first is a cell with multiple date values in
text format (YYYY/MM/DD). The other I was planning to be a Calculated cell
that I thought I'd use to compare the string of numbers and determine which
is the next in sequence after today's date. As there may be numbers in the
past I cannot just use the first item.

The cell of text looks like this:
2009-03-27 00:00:00
2009-04-10 00:00:00
2009-04-29 00:00:00
2009-05-13 00:00:00
2009-05-27 00:00:00

The calculated cell should output:
2009-04-10 00:00:00

Problem: I've been unable to figure out a calculation that will accomplish
this. The idea I had was to examine the text in pieces and compare them
numerically to "today" (as a number instead of a date). I'm not sure how to
do this though.

I'm not very adept with Excel functions. I've started with this:
"=(MID(A2,2,10))" but all it does is return the first item. I also tried
using a Replace function to get rid of the dashes and also one that would
return the dates as a decimal. But while I can get the numbers to change
format, I don't know how to do the compare...

Any help on this would be most appreciated!

 

[Fred Smith]

Do a Vlookup using:

=vlookup(text(today(),"yyyy-mm-dd"),a:a,1,true)

Adjust the range to suit.

Regards,
Fred.

 

[richardlpalmer]

Hi Fred, thank you very much for the reply. I'm not quite sure what to do
with that. When you say to adjust the range to suit what should I do? I'm not
very familiar with this at all...

Here's a screen shot of the range of cells and what I'd like to get out of
them:
http://i169.photobucket.com/albums/u227/richardlpalmer/Screen Shots/28.jpg

Thanks again. I really do appreciate the help.

Richard

 

[Fred Smith]

Hi Richard,

Sorry, I misread your post. I didn't catch the fact that all your data was
in one cell. This will make it very difficult to do what you want.

If it's required that the data remain in one cell, the only solution I can
see is a macro. If this is the route you want to go, then post to the
..programming group and I'm sure someone will help you.

If it were me, I would change the data so that each date is in a separate
cell. Then the vlookup will work. "Adjust the range to suit" means you have
to modify the formula as appropriate for your situation. If your dates are
in a2:a5, for example, you would change my formula to:
=vlookup(text(today(),"yyyy-mm-dd"),a2:a5,1,true)

Regards,
Fred.