# Thread: Date extraction Thanks: 0 Likes:  1 Post #4990840 (1)

1. ## Date extraction

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?

2. ## Re: Date extraction

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

 A B 1 LAX Marketing Jan 17 True Up Jan-17

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

Excel tables to the web >> Excel Jeanie HTML 4

3. ## Re: Date extraction

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

4. ## Re: Date extraction

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))

5. ## Re: Date extraction

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.

6. ## Re: Date extraction

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))

7. ## Re: Date extraction

Try this for end of month.

 A B 1 LAX Marketing Jan 17 True Up 1/31/17

 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),"")

Excel tables to the web >> Excel Jeanie HTML 4

8. ## Re: Date extraction

Originally Posted by AhoyNC
Try this for end of month.

 A B 1 LAX Marketing Jan 17 True Up 1/31/17

 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),"")

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?

9. ## Re: Date extraction

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 CTRL-SHIFT-ENTER.

 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

 Cell Formula B1 {=IFERROR(EOMONTH(DATEVALUE(SUBSTITUTE(MID(A1,MAX(IFERROR(FIND({"Jan ","Feb ","Mar ","Apr ","May ","Jun ","Jul ","Aug ","Sep ","Oct ","Nov ","Dec "},A1),0)),6)," "," 1, ")),0),"")}
Formula Array:
Produce enclosing
{ } by entering
formula with CTRL+SHIFT+ENTER!

Excel tables to the web >> Excel Jeanie HTML 4

10. ## Re: Date extraction

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)

 A B 1 LAX Marketing Jan 17 True Up 31/01/17 2 LAX Marketing True Up Through Feb 17 28/02/17 3 LAX Marketing True Mar 17Up Through 31/03/17 4 Apr 17 LAX Marketing True Up Through 30/04/17