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!

 

[barry houdini]

Assuming your dates are in ascending order as they appear to be in your examples this formula would also return the next date on or after today......but without CTRL+SHIFT+ENTER

=INDEX(A1:A5,MATCH(TODAY(),INDEX(A1:A5+0,0))+(LOOKUP(TODAY(),A1:A5+0)<>TODAY()))+0

 

[richardlpalmer]

Possibly you haven't applied CTRL+SHIFT+ENTER

Select cell with formula, press F2 key then hold down CTRL and SHIFT keys and press ENTER. If done correctly you'll see curly braces, i.e. formula will look like this:

{=MIN(IF(A1:A5+0>=TODAY(),A1:A5+0))}

Okay, just to be clear I changed your formula to "A2" because A1 had text in it. I then went to a blank cell, pressed F2, pasted the formula.

Then did CTRL + Shift + Enter.

 

[maniu]

yeah, exactly as ii thought, i dont think you can do min on cell that has multiple values as text delimited by "enters", thats why I applied [clean] formula first, then got rid of hours and minutes, then splitted each date in a separate cell and found the minimum next to today, but I dont think you can put it in one cell.
Here what are the formulas, assuming that A1 is the cell with data:
[B1]=CLEAN(A1)
[C1]=SUBSTITUTE(B1," 00:00:00","")
[D1]=LEFT(C1,10)
[E1]=SUBSTITUTE(C1,D1,"")
[F]=LEFT(E1,10)
[G]=SUBSTITUTE(E1,F1,"")
[H]=LEFT(G1,10)
=SUBSTITUTE(G1,H1,"")
[J]=LEFT(I1,10)
[K]=SUBSTITUTE(I1,J1,"")
[L]=D1+1
[M]=F1+1
[N]=H1+1
[O]=J1+1
[P]=K1+1
[Q]=TODAY()
[R]=IF((L1-1)>$Q$1,L1-1,99999)
=IF((M1-1)>$Q$1,M1-1,99999)
[T]=IF((N1-1)>$Q$1,N1-1,99999)
=IF((O1-1)>$Q$1,O1-1,99999)
[V]=IF((P1-1)>$Q$1,P1-1,99999)
[W]=MIN(R1:V1)



I know it's ugly, but dont have time to optimize it just hide all those columns, leave the last one with the result

 

[barry houdini]

OK, sorry, I obviously didn't read Mike's posts closely enough (or yours!). I made the assumption that you have 5 dates in 5 cells.....not all in one.....so my suggestions won't work for that.......

 

[richardlpalmer]

yeah, exactly as ii thought, i dont think you can do min on cell that has multiple values as text delimited by "enters", thats why I applied [clean] formula first, then got rid of hours and minutes, then splitted each date in a separate cell and found the minimum next to today, but I dont think you can put it in one cell.
Here what are the formulas, assuming that A1 is the cell with data:
[B1]=CLEAN(A1)
[C1]=SUBSTITUTE(B1," 00:00:00","")
[D1]=LEFT(C1,10)
[E1]=SUBSTITUTE(C1,D1,"")
[F]=LEFT(E1,10)
[G]=SUBSTITUTE(E1,F1,"")
[H]=LEFT(G1,10)
=SUBSTITUTE(G1,H1,"")
[J]=LEFT(I1,10)
[K]=SUBSTITUTE(I1,J1,"")
[L]=D1+1
[M]=F1+1
[N]=H1+1
[O]=J1+1
[P]=K1+1
[Q]=TODAY()
[R]=IF((L1-1)>$Q$1,L1-1,99999)
=IF((M1-1)>$Q$1,M1-1,99999)
[T]=IF((N1-1)>$Q$1,N1-1,99999)
=IF((O1-1)>$Q$1,O1-1,99999)
[V]=IF((P1-1)>$Q$1,P1-1,99999)
[W]=MIN(R1:V1)



I know it's ugly, but dont have time to optimize it just hide all those columns, leave the last one with the result

Clean I don't particularly care about. I just need to know where to paste this stuff!

Is my assumption correct that each letter in a bracket is a new column/cell?

OK, sorry, I obviously didn't read Mike's posts closely enough (or yours!). I made the assumption that you have 5 dates in 5 cells.....not all in one.....so my suggestions won't work for that.......

No worries at all. I'm just so appreciative of the help you're all offering. It's huge for me! I'm a SharePoint guy (with some knowledge of InfoPath) but these formulas are not something typically in my scope (although they're useful in SharePoint as well).

 

[maniu]

yeah, [B1], [C1] down to [W1] are cells, just copy those formulas in them

 

[barry houdini]

With all data in a single cell, A2 try this formula to get the next date on or after today......

=MIN(IF(MID(A2,{1,21,41,61,81},19)+0>=TODAY(),MID(A2,{1,21,41,61,81},19)+0))

again confirmed with CTRL+SHIFT+ENTER

It works with my sample data but might need tweaking if you have any extra spaces or other characters in your data, see below

Book1
	A	B	C	D
1
2	2009-03-2700:00:002009-04-1000:00:002009-04-2900:00:002009-05-1300:00:002009-05-2700:00:00	4/10/2009
3
4
Sheet2

 

[richardlpalmer]

With all data in a single cell, A2 try this formula to get the next date on or after today......

=MIN(IF(MID(A2,{1,21,41,61,81},19)+0>=TODAY(),MID(A2,{1,21,41,61,81},19)+0))

again confirmed with CTRL+SHIFT+ENTER

It works with my sample data but might need tweaking if you have any extra spaces or other characters in your data, see below

Book1
	A	B	C	D
1
2	2009-03-2700:00:002009-04-1000:00:002009-04-2900:00:002009-05-1300:00:002009-05-2700:00:00	4/10/2009
3
4
Sheet2

Weird, I started with a fresh workbook and this worked fine. Thank you very much! I'm starting with Excel but now have to figure out how to translate this into SharePoint (I know, that's not your guys' problem -- there's just so much more knowledgeable help within Excel than there seems to be in SharePoint).

What do the "{}" brackets do in Excel? Can they be replaced with "()" by any chance?

 

[barry houdini]

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

 

[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

I see. So if it had 8 or 12 dates this would accomodate it.