Formula error

[Jacky]

Hi,

I was just trying to create a formula to automatically calculates the date
from the pasted in value, example: Oct 22 2008 02:33 pm

Considering the value is in cell B2, I'm using the following formula to
extract the value to compose the date:
=Date(mid(B2,8,4),Match(Left(B2,3),E1:E12,F1:F12),mid(B2,5,2))

Noticed that there is a Match function in the formula, as I understand that
text is not auto recognised for value of month. A reference table is prepared
to match the text extracted:
E F
1 Jan 1
2 Feb 2
3 Mar 3
4 Apr 4
5 May 5
6 Jun 6
7 Jul 7
8 Aug 8
9 Sep 9
10 Oct 10
11 Nov 11
12 Dec 12

The outcome from formula will give me the date 22 Aug 2008, which the month
is wrong.

Further testing shows that extracted value by Left(B2,3) gives 10, but when
used in Match(Left(B2,3),E1:E12,F1:F12), the value returned is 8.

Why is it so?
Is there anyway to correct it?

 

[John C]

Instead of using the MATCH function, instead, try this for the month portion
--TEXT(LEFT(B2,3)&" 1, 2000","m")

Hope this helps.

 

[Sheeloo]

Use
=MATCH(LEFT(B2,3),E1:E12,0)

Third parameter should be 0 for exact match.

 

[T. Valko]

Another one:

=DATE(MID(B2,8,4),MONTH(LEFT(B2,3)&1),MID(B2,5,2))

If the year is *always* 2008 and the day is *always* in 00 format:

=DATEVALUE(LEFT(B2,6))

Format as Date

 

[T. Valko]

If the year is *always* 2008

That should be:

If the year is *always* the current year...