|
Formula error
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? |
Formula error
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? |
Formula error
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? |
Formula error
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? |
Formula error
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? |
| All times are GMT +1. The time now is 05:46 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com