Caluclating Dates From Text

[richardlpalmer]

Goal: I would like a column to display a "Next" date by comparing text values in one column to "today". This is a rough mock-up of what I would like to end up with (ideally without the zeros for h/m/s but I'm not too picky at this point):

Scenario: I have two columns. The first is a column with multiple date values in text format. The other column (so far) is a Calculated column 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.



Problem: MOSS will not allow multiple dates in a Date and Time column. Unless there's a way to have multiple Dates within a column I don't know of an alternative to using a Single Line text column.

I've been unable to figure out a calculation that will accomplish this. An 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've started with this: "=(MID(Dates,2,10))" but all it does is return the first item.



Additional Data: This may or may not be useful information.
[ul]
[li]I am using sharepoint list of dates to populate a multiple selection list box in an InfoPath form.[/li]
[li]Users of the InfoPath form can select multiple dates within the form and submit.[/li]
[li]Submitting the form publishes to a form library in SharePoint.[/li]
[li]An SPD workflow updates the above list.[/li]
[/ul]

Any assistance on this would be greatly appreciated. Thank you in advance.

 

[richardlpalmer]

Bump

 

[richardlpalmer]

Okay, so I got it to work in Excel and am hoping someone here will be able to help translate this into something SharePoint might be happy with.

=MIN(IF(MID(A2,(ROW(INDIRECT("1:"&(LEN(A2)+1)/20))-1)*20+1,19)+0>=TODAY(),MID(A2,(ROW(INDIRECT("1:"&(LEN(A2)+1)/20))-1)*20+1,19)+0))

This outputs the date in number format (and I've formatted the cells to display in date format -- I was planning on telling SharePoint to do the same).

"A2" is a cell in Excel. I was planning on changing that to "Dates", which is my column in SP. That didn't work though but I don't believe it's because I named the column. It's one of the other parts of that calculation.

Help!