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.

 

[yky]

What does NA stand for and which day do you want, the first or last?

 

[Peter_SSs]

So, assuming those 3 cells are in A1:A3, does this work for you?

=DATEVALUE("1"&MID(SUBSTITUTE(A2,"'",""),4,5))

If not please give more examples and expected results & any further explanation that you can

 

[AMIT2179]

Basically, lets assume, B1 has value "Dec, 2014", B2 has value "NA_Dec'14" and B3 has value "NA_". I am looking for a formulae in column C, which gives me a blank or false in C1, date as in last day of Dec 14 in C2 and blank or NA in C3. So when the formulae finds a combination of NA and Month,Year like in B2, the formulae returns a date as in last day of the month specified in cell B2.

 

[Peter_SSs]

In that case try this in C1, copied down

=IFERROR(EOMONTH(MID(SUBSTITUTE(B1,"'",""),4,5),0),"")

 

[AMIT2179]

Thanks, it surely gives me last day of the month but its giving me year as 2019 in all cases. Please help.

 

[Peter_SSs]

Thanks, it surely gives me last day of the month but its giving me year as 2019 in all cases. Please help.

Ah, must be different regional date settings to me I think. Try this instead.

=IF(AND(LEFT(B1,3)="NA_",LEN(B1)>3),EOMONTH(1&MID(SUBSTITUTE(B1,"'",""),4,5),0),"")

 

[AMIT2179]

This works, thanks a lot.

 

[Peter_SSs]

This works, thanks a lot.

You're welcome. Thanks for the confirmation.

 

[AMIT2179]

One more help needed. In one column i have multiple email addresses of different length. For eg: amit.dhamija@rediffmail.com. How can i extract value after "@" and before "." which in this case is "rediffmail" ??