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 columns. The first column contains cells with multiple date values in text format (YYYY/MM/DD) in each cell. The other I was planning to be Calculated cells that I thought would compare the string of text (possibly converted to 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 (based on today's date) 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 yet. 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!

 

[richardlpalmer]

Sorry I don't know anything about Sharepoint...

Note: that formula will only work with 5 dates in a cell, you can switch to this version to accommodate different numbers of dates

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

still confirmed with CTRL+SHIFT+ENTER

If/when you get a chance, could you explain what the individual parts of that formula do? It works brilliantly in Excel. If I knew what the pieces did I might be able to translate it into a format that I could use elsewhere.

 

[richardlpalmer]

If/when you get a chance, could you explain what the individual parts of that formula do? It works brilliantly in Excel. If I knew what the pieces did I might be able to translate it into a format that I could use elsewhere.

What I mean is what are the various functions happening in that string?

 

[richardlpalmer]

Sorry I don't know anything about Sharepoint...

Note: that formula will only work with 5 dates in a cell, you can switch to this version to accommodate different numbers of dates

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

still confirmed with CTRL+SHIFT+ENTER

From what I can see the first part is finding what is the minimum value.

The next part seems to be ascertaining the numbers of characters but I don't know what the "1:"& part means.

Then it seems to be taking 19 characters, adding 1 to the number and then multiplying by 20. Then reversing the process to get a number in decimal form. Not totally sure what's happening here though...

Then it's using that number (date) and comparing it today to see if it's greater than.

If so, I'm not sure what happens next -- maybe it's put back into date format or perhaps just a number? This part confuses me.

The last part must be the "ELSE" piece, which if the number isn't greater than today will go to the next group of 19 characters, etc.

Could you please help clarify these pieces of the puzzle for me?