Extracting a date from a cell which has a particular value

Status
Not open for further replies.

AMIT2179

New Member
Joined
Nov 2, 2011
Messages
42
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

Well-known Member
Joined
Jun 7, 2011
Messages
1,450
What does NA stand for and which day do you want, the first or last?
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
41,800
Office Version
365
Platform
Windows
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

New Member
Joined
Nov 2, 2011
Messages
42
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
41,800
Office Version
365
Platform
Windows
In that case try this in C1, copied down

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

AMIT2179

New Member
Joined
Nov 2, 2011
Messages
42
Thanks, it surely gives me last day of the month but its giving me year as 2019 in all cases. Please help.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
41,800
Office Version
365
Platform
Windows
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),"")
 
Status
Not open for further replies.

Forum statistics

Threads
1,078,437
Messages
5,340,277
Members
399,361
Latest member
Linford

Some videos you may like

This Week's Hot Topics

  • Problem with Radio Button's format control
    I am creating an employee evaluation template (a sample is below) Column A is the category Column B, C D, E and F will be ratings (unacceptable...
  • Last Display on userform to a Listbox
    [CODE=vba] lstdisplay.ColumnCount = 15 lstdisplay.RowSource = "A1:O600000" [/CODE] So when i do this it Displays everything on the sheet i am...
  • Rename and move files to a new location
    Dear all, I have an excel file with the following information. The actual file name is at column A but i want to rename it using the following...
  • Help with True/False Formula
    Hello! Am stumped how to fix this formula, in which my result returns 'True', but it should return False. =IF(AG2=True...
  • Clear extra characters from a provided range of cells
    Dear All, I have following code which gives me desired output to remove extra characters from a provided range. But it takes too much time when...
  • Help with Current and highest streaks
    Hi there, I've just joined the forum and this is my first post. I've already spent quite a bit of time searching the net and this forum for a...
Top