Date Function

[Jen_T]

If a cell has text and numeric (date) is there a way to pull the date out
easily ?
E.g.
Product dropped off on 11/01/2009 by Michelle Smith
I would like to see 11/01/2009

Thank you

 

[T. Valko]

Try this...

=LOOKUP(1E100,--MID(A2,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&"0123456789")),ROW(INDIRECT("1:"&LEN(A2)))))

Format as Date

 

[Luke M]

=DATEVALUE(MID(A2,FIND("/",A2)-2,10))

Format as date.

 

[Ron@Buy]

Here's some possible leads:
http://office.microsoft.com/en-us/excel/HA011549011033.aspx
http://www.meadinkent.co.uk/xlextracttext.htm

 

[Ron@Buy]

Brilliant Biff

Try this...

=LOOKUP(1E100,--MID(A2,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&"0123456789")),ROW(INDIRECT("1:"&LEN(A2)))))

Format as Date

--
Biff
Microsoft Excel MVP





.

 

[Ron@Buy]

Luke,
Very exceptable unless there are no other numbers in the sentence e.g.
Product no. 1 dropped off on 11/01/2009 by Michelle Smith

 

[Jen_T]

Thank you, Luke,
How does one read the formula ? I do not quite understand how this one
works. But it worked beautifully, can you explain ?

Thank you

 

[Ron@Buy]

I'll try again:
Luke,
Very axceptable unless there are other numbers in the sentence e.g.
Product no. 1 dropped off on 11/01/2009 by Michelle Smith

 

[T. Valko]

Thanks!

But, I think I'm using an atomic bomb to kill an ant!

That formula is a generic formula to extract a number from a string.

I like Luke's suggestion but it needs tweaked a bit.

Let's assume that there are no other numbers in the string and the date is
*always* in the format m/d/yyyy or m/dd/yyyy.

Luke's formula could fail when the date is at the start of the string.

Consider these strings:

1/1/2010 is the deadline
1/10/2010 is the deadline
10/1/2010 is the deadline
10/10/2010 is the deadline

The deadline is 1/1/2010
The deadline is 1/10/2011
The deadline is 10/1/2010
The deadline is 10/10/2010

The deadline of 1/1/2010 is firm
The deadline of 1/10/2010 is firm
The deadline of 10/1/2010 is firm
The deadline of 10/10/2010 is firm

So, Luke's formula with a tweak will account for all of the above:

=--TRIM(MID(" "&A1,FIND("/"," "&A1)-2,10))

 

[T. Valko]

Well, this formula has a potential flaw *if* the date is followed by another
character like a punctuation mark.

Maybe the "atomic option" is best afterall.

 

[Rick Rothstein]

Here is a slightly different "atomic option" from the one you posted which
does not contain the Volatile INDIRECT function call (plus it's 2 characters
shorter<g>)...

=LOOKUP(1e100,--LEFT(MID(A2,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&"0123456789")),LEN(A2)),ROW($1:$99)))

 

[T. Valko]

does not contain the Volatile INDIRECT function

ROW($1:$99)

Well, you can either use the volatile INDIRECT and be robust against row
insertions or you can use ROW and hope you don't ever need to insert rows
from row 1 to 99.

Of course, if the date is *always* in a specific format where the length of
the date string is *always* the same then it could be as simple as:

=--MID(A1,FIND("/",A1)-n1,n2)

Where n1 = 1 or 2, the length of the month portion of the date and n2 = the
total length of the date string.

This would be very easy if there was a SUBSTITUTE / REPLACE type function
that would take arrays as the old_text argument!

 

[Rick Rothstein]

Insertions do not seem to affect either of our formulas as far as I can see.

 

[T. Valko]

A1 = 1/1/2009 is the start date.

It might be far-fetched but if you inserted 9 or more new rows at the top of
the sheet then ROW($1:$99) becomes ROW($10:$108) etc. Then LEFT starts with
10 characters and the date portion by itself is never evaluated.

 

[Rick Rothstein]

Okay, I see what you mean.