Extracting a date from a cell which has a particular value

[AMIT2179]

I have 3 cells in excel in same column. One has value "Dec, 2014", second one has value "NA_Dec'14" and the third one has value "NA_". As you can see 3 cells have 3 different values. I am looking to extract a date either first day of the month or last day of the month from the second cell which has value "NA_Dec'14". So the output should look like either 12/01/14 or 12/31/14 or anyday of the Dec 14. Please can you help.

 

[Peter_SSs]

One way:

<b>Extract</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Arial,Arial; font-size:8pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:217px;" /><col style="width:87px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="font-size:10pt; ">amit.dhamija@rediffmail.com</td><td style="font-size:10pt; ">rediffmail</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b>Spreadsheet Formulas</b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >B1</td><td >=REPLACE(LEFT<span style=' color:008000; '>(A1,FIND<span style=' color:#0000ff; '>(".",A1,FIND<span style=' color:#ff0000; '>("@",A1)</span>)</span>-1)</span>,1,FIND<span style=' color:008000; '>("@",A1)</span>,"")</td></tr></table></td></tr></table>

 

[AMIT2179]

Thanks, this worked perfectly.

 

[Peter_SSs]

Thanks, this worked perfectly.

You're welcome. Thanks for the follow-up.

 

[AMIT2179]

One more question, related to "Extraction of date". I have a values like "Dec, 2019", "Sep, 2018" etc in a cell A1, A2 and so on, how do i convert it into a date (depending on regional setting dd/mm/yy or mm/dd/yy) in a new cell B1, B2 and so on?

 

[Peter_SSs]

Try
=(1&A1)+0

 

[AMIT2179]

Sorry, this doesnt work. Is there some formulae which works only in case the length of string in A1 is 9 then it should convert something like Dec, 2019 into 31/12/2019 and if the length of string is more than 9, it should just give blank?

 

[Peter_SSs]

Sorry, this doesnt work. Is there some formulae which works only in case the length of string in A1 is 9 then it should convert something like Dec, 2019 into 31/12/2019 and if the length of string is more than 9, it should just give blank?

When giving sample data & expected results, please ensure you give enough to show the variety that actually exists in your real data. In this latest question the only variety you gave was that the month and year could be different. Remember, all we have to go on is what you tell us or show us. In this instance you also did not tell us that you wanted the last date in the month, just "how do i convert it into a date"

Try this instead
=IF(LEN(A1)=9,EOMONTH(1&A1,0),"")

 

[AMIT2179]

I agree Peter. My mistake. I will surely do so from now on. Here are some of the values which exist in Column A. The desire is to return a date in case the cell has 9 characters or in other words the formula gives the date only in case NA is not there in the cell. The one you provided above only returns #Value in case the cell only has NA_ and blank in all other instances. Please help.

Apr, 2015

NA_

Apr, 2015

Apr, 2015

NA_

Apr, 2015

Apr, 2015

NA_Feb'16

<colgroup><col></colgroup><tbody>
</tbody>

 

[Peter_SSs]

The one you provided above only returns #Value in case the cell only has NA_ and blank in all other instances.

The formula I gave in post 17 doesn't do that for me. Here are my results with that formula in column B. Is row 8 also supposed to return a date like in the earlier part of this thread?
If so, try the formula in column C. That formula would still result in an error if the column A has 9 characters but they are not in the format of either A1 or A8.

<b>Convert to Date</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Arial,Arial; font-size:8pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:119px;" /><col style="width:109px;" /><col style="width:107px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="font-size:10pt; text-align:right; ">Apr, 2015</td><td style="font-size:10pt; text-align:right; ">30/04/2015</td><td style="font-size:10pt; text-align:right; ">30/04/2015</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="font-size:10pt; ">NA_</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="font-size:10pt; text-align:right; ">Apr, 2015</td><td style="font-size:10pt; text-align:right; ">30/04/2015</td><td style="font-size:10pt; text-align:right; ">30/04/2015</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="font-size:10pt; text-align:right; ">Apr, 2015</td><td style="font-size:10pt; text-align:right; ">30/04/2015</td><td style="font-size:10pt; text-align:right; ">30/04/2015</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="font-size:10pt; ">NA_</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td style="font-size:10pt; text-align:right; ">Apr, 2015</td><td style="font-size:10pt; text-align:right; ">30/04/2015</td><td style="font-size:10pt; text-align:right; ">30/04/2015</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td style="font-size:10pt; text-align:right; ">Apr, 2015</td><td style="font-size:10pt; text-align:right; ">30/04/2015</td><td style="font-size:10pt; text-align:right; ">30/04/2015</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td style="font-size:10pt; ">NA_Feb'16</td><td style="font-size:10pt; ">#VALUE!</td><td style="font-size:10pt; text-align:right; ">29/02/2016</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b>Spreadsheet Formulas</b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >B1</td><td >=IF(LEN<span style=' color:008000; '>(A1)</span>=9,EOMONTH<span style=' color:008000; '>(1&A1,0)</span>,"")</td></tr><tr><td >C1</td><td >=IF(LEN<span style=' color:008000; '>(A1)</span>=9,IFERROR<span style=' color:008000; '>(EOMONTH<span style=' color:#0000ff; '>(1&A1,0)</span>,EOMONTH<span style=' color:#0000ff; '>(1&MID<span style=' color:#ff0000; '>(SUBSTITUTE<span style=' color:#804000; '>(A1,"'","")</span>,4,5)</span>,0)</span>)</span>,"")</td></tr></table></td></tr></table>

 

[AMIT2179]

Thanks Peter, but i am not sure why this is still giving me #Value in each cell. No A8 should not return a date as the string in more than 9 characters long. Please help.