Calculated Column Formula

Question

Goal: Translating the functionality of a formula that works in Excel, into something I can use in a calculated column in SharePoint.

Alternate Goal: A completely different solution from the one I'm currently trying to make work.

Scenario: Running MOSS 2007, I have a list with a text column populated with multiple dates (in text format) for each item in the list.

I need to extract one date from the block of text for each item – the date greater than or equal to today’s date. I was unable to figure this out within SharePoint but someone offered a solution in Excel. The formula below does in Excel what I wish to do in SharePoint.

=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))

Problem: I’ve been unable to convert the above Excel formula into something I can use in SharePoint. I decided to break it down into 2 pieces instead of one large one. Starting with the IF portion I tried the following, with “[Dates]” being the text column:

=MIN(IF(MID([Dates],(ROW(INDIRECT("1:"&(LEN[Dates]+1)/20))-1)*20+1,19)+0>=TODAY(),”False”))

I realize there is no “ROW” or “INDIRECT” function in SharePoint but was hoping to get some output that I could evaluate. I then tried:

=MIN(IF(MID([Dates],( ("1:"&(LEN[Dates]+1)/20)-1)*20+1,19)+0>=TODAY(),”False”))

This receives a general error but I don’t know what to replace the “ROW” and “INDIRECT” functions with.

Any assistance on solving this would be appreciated.

Answer 1

Would a better approach be a coded one? I wondering if this could be done in Access much easier (although I'm not an Access person). Would Access be able to parse the text, do the calculation and allow for more uses of the data (for instance if the client wanted to sort by month or search for specific dates, etc.)?

If so, how difficult would this be for an Access developer? And could it be used within SharePoint? I've never done anything with Access and SharePoint (outside of editing list data with the Edit in Access action built into SharePoint).