Date extraction

[jeongs1]

If I have "LAX Marketing Jan 17 True Up", what is the formula that I can use to return the result as "Jan-17"?

I currently have
:"IFERROR(EOMONTH(DATEVALUE(SUBSTITUTE(MID(A1,MAX(IFERROR(FIND({"Jan ","Feb ","Mar ","Apr ","May ","Jun ","Jul ","Aug ","Sep ","Oct ","Nov ","Dec "},A1),0)),6)," ","-")),0),"")"

but whenever I'd use the formula, it'd return as "Jan-18" instead of "Jan-17". What am I doing wrong?

 

[AhoyNC]

Try:
In your formula 17 is coming in as the day and not year.

Excel Workbook
	A	B
1	LAX Marketing Jan 17 True Up	Jan-17
Sheet

 

[jeongs1]

The formula does work! But, what if I want it to be the end of the month, rather than the 1st of the month?

 

[admiral100]

Hi,

If you don't mind the result will bei in text format :
=MID(A1,FIND("*",SUBSTITUTE(A1," ","*",{2,4}))+1,6)

Or in date format :

=DATE(2018,MONTH(MID(A1,FIND("*",SUBSTITUTE(A1," ","*",{2,4}))+1,6)),RIGHT(MID(A1,FIND("*",SUBSTITUTE(A1," ","*",{2,4}))+1,6),2))

 

[jeongs1]

Hi,

If you don't mind the result will bei in text format :
=MID(A1,FIND("*",SUBSTITUTE(A1," ","*",{2,4}))+1,6)

Or in date format :

=DATE(2018,MONTH(MID(A1,FIND("*",SUBSTITUTE(A1," ","*",{2,4}))+1,6)),RIGHT(MID(A1,FIND("*",SUBSTITUTE(A1," ","*",{2,4}))+1,6),2))

What if the cell contains prior year? The formula returns the result as Jan 18 when I want it to pick up the year in the cell.

 

[admiral100]

I thought that 17 is the day .... so just a month and a year ?

Text version : =TEXT(MID(A1,FIND("*",SUBSTITUTE(A1," ","*",{2,4}))+1,6),"mm/yy")
Date version: =DATEVALUE(MID(A1,FIND("*",SUBSTITUTE(A1," ","*",{2,4}))+1,6))

 

[AhoyNC]

Try this for end of month.

Excel Workbook
	A	B
1	LAX Marketing Jan 17 True Up	1/31/17
Sheet

 

[jeongs1]

Try this for end of month.

	A	B
LAX Marketing Jan 17 True Up

<tbody>
[TD="align: center"]1[/TD]

[TD="align: right"]1/31/17[/TD]

</tbody>

Spreadsheet Formulas

Cell Formula B1 =IFERROR(EOMONTH(DATEVALUE(SUBSTITUTE(MID(A1,FIND({"Jan ","Feb ","Mar ","Apr ","May ","Jun ","Jul ","Aug ","Sep ","Oct ","Nov ","Dec "},A1),6)," "," 1, ")),0),"") <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

Yay! It worked! Thanks!

Now, what if on A2, the description is different from A1, but I want to use only one formula?

For an instance, A2 may have:

LAX Marketing True Up Through Feb 17


If I want Feb-17 (again, 17 being the year,) how would I mimic the formula?

 

[AhoyNC]

I should have checked for other months. This is almost back to your original formula.
This is an array formula and must be entered with

Excel Workbook
	A	B
1	LAX Marketing Jan 17 True Up	1/31/2017
2	LAX Marketing True Up Through Feb 17	2/28/2017
3	LAX Marketing True Mar 17Up Through	3/31/2017
4	Apr 17 LAX Marketing True Up Through	4/30/2017
CTRL-SHIFT-ENTER.

 

[admiral100]

Or this one:-

Ctrl+Shift+Enter NOT just Enter

B1 =EOMONTH(MID(A1,MIN(IFERROR(0+(MID($A1,ROW($A$1:$A$100),1))*ROW($A$1:$A$100),100))-4,6),0)

[TABLE="width: 404"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]LAX Marketing Jan 17 True Up[/TD]
[TD]31/01/17[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]LAX Marketing True Up Through Feb 17[/TD]
[TD]28/02/17[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]LAX Marketing True Mar 17Up Through[/TD]
[TD]31/03/17[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Apr 17 LAX Marketing True Up Through[/TD]
[TD]30/04/17[/TD]
[/TR]
</tbody>[/TABLE]