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!

 

[Scott R]

If your date is in A1, you can use DATEVALUE(A1) or A1+0 to use that date's value in a formula. TODAY() will get you today's date value.

=TODAY()-DATEVALUE(A1)
might be something you could use or tweak a bit

I couldn't tell why 4/10/09 was your desired output in your example. Can you post more examples?

 

[richardlpalmer]

If your date is in A1, you can use DATEVALUE(A1) or A1+0 to use that date's value in a formula. TODAY() will get you today's date value.

=TODAY()-DATEVALUE(A1)
might be something you could use or tweak a bit

I couldn't tell why 4/10/09 was your desired output in your example. Can you post more examples?

Thank you for the reply. So far I've not been able to get an output with it but I'll keep trying. I don't think it's happy with the fact there are multiple dates in text format -- I think it just sees it as a bunch of text, not dates.

The reason it was the desired output is it's the next date after today. As today is the 30th the next date is 4/10/09.

Here's a screen shot of the desired output from 4 cells. Notice each cell has multiple dates (in text form).

 

[maniu]

first, get rid of unneeded characters, do the [B1]=clean(A1).
If you need hours and minutes, don't do this, if not then [C1]=SUBSTITUTE(B1," 00:00:00","")
then i suggest the following sequence,
[D1]=LEFT(C1,10) (thats with "00:00:00" taken off)
[E1]=SUBSTITUTE(C1,D1,"")
[F1]=LEFT(E1,10)
[G1]=SUBSTITUTE(E1,F1,"")
and so on till you got one date left in [K1], but they're still are in wrong format, so if comparing to the present date, it wont work properly, what I did was:
[L1]=D1+1 which gave me [4/29/2009]
same with other cells till I got [5/28/2009] in [P1] cell
then [Q1]=today(), next couple of cells you compare the dates to today's date, like [R1]=IF((L1-1)>$Q$1,L1-1,99999) (don't forget to substract 1 back)
and then just do [W1]=MIN(R1:V1)

of course its too much i'm sure there can be other more optimized solutions but it's first that i could think about, i used excel 2007
GL

 

[mikerickson]

Is that sample the contents of one cell or 5 cells?
You mentioned that it is text. Unfortunatly, DATEVALUE doesn't interpret that format as a date

=DATEVALUE("2009-03-27 00:00:00") returns an error,
as does =DATEVALUE("2009-03-27")

I see that you were answering my questions as I posted.
Can you change from the multiple dates in one cell format?

 

[barry houdini]

If your "dates" are in A1:A5 then use this formula to get the first date greater than or equal to today:

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

This is an "array formula" which needs to be confirmed with CTRL+SHIFT+ENTER so that curly braces appear around the formula in the formula bar.

Format result cell as date

 

[richardlpalmer]

first, get rid of unneeded characters, do the [B1]=clean(A1).
If you need hours and minutes, don't do this, if not then [C1]=SUBSTITUTE(B1," 00:00:00","")
then i suggest the following sequence,
[D1]=LEFT(C1,10) (thats with "00:00:00" taken off)
[E1]=SUBSTITUTE(C1,D1,"")
[F1]=LEFT(E1,10)
[G1]=SUBSTITUTE(E1,F1,"")
and so on till you got one date left in [K1], but they're still are in wrong format, so if comparing to the present date, it wont work properly, what I did was:
[L1]=D1+1 which gave me [4/29/2009]
same with other cells till I got [5/28/2009] in [P1] cell
then [Q1]=today(), next couple of cells you compare the dates to today's date, like [R1]=IF((L1-1)>$Q$1,L1-1,99999) (don't forget to substract 1 back)
and then just do [W1]=MIN(R1:V1)

of course its too much i'm sure there can be other more optimized solutions but it's first that i could think about, i used excel 2007
GL

Thanks for the input. It might be right what I need but I'm not following everything. Can you please simplify it based on the screen shot above? Assume A2 as the source and B2 as where the result should go. I'd like to simply paste what you come up with in B2 and see what happens.

 

[richardlpalmer]

Is that sample the contents of one cell or 5 cells?
You mentioned that it is text. Unfortunatly, DATEVALUE doesn't interpret that format as a date

=DATEVALUE("2009-03-27 00:00:00") returns an error,
as does =DATEVALUE("2009-03-27")

I see that you were answering my questions as I posted.
Can you change from the multiple dates in one cell format?

See the screen shot lower on the page. That text is all in one cell. That's why I was thinking of converting things to numbers (from text) and then compare from there. Just don't know how to...

 

[richardlpalmer]

If your "dates" are in A1:A5 then use this formula to get the first date greater than or equal to today:

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

This is an "array formula" which needs to be confirmed with CTRL+SHIFT+ENTER so that curly braces appear around the formula in the formula bar.

Format result cell as date

Hmmmm, trying this but I'm getting #VALUE! as output.

 

[barry houdini]

Hmmmm, trying this but I'm getting #VALUE! as output.

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