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?

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

Hope this helps.
--
** John C **

"Jacky" wrote:

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?

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

Third parameter should be 0 for exact match.


"Jacky" wrote:

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?

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


--
Biff
Microsoft Excel MVP


"Jacky" wrote in message
...
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?

If the year is *always* 2008

That should be:

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

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
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


--
Biff
Microsoft Excel MVP


"Jacky" wrote in message
...
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?