# Calculating Dates From Text

#### richardlpalmer

##### New Member
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!

### Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

#### Scott R

##### Active Member
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

##### New Member
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

##### New Member
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

##### MrExcel MVP
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

##### MrExcel MVP
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

##### New Member
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

##### New Member
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

##### New Member
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

##### MrExcel MVP
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))}

Replies
1
Views
143
Replies
1
Views
170
Replies
1
Views
333
Replies
1
Views
324
Replies
6
Views
1K

1,195,651
Messages
6,010,932
Members
441,574
Latest member
Prescience

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back