Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: Date extraction

  1. #1
    New Member
    Join Date
    May 2017
    Posts
    44
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #2
    Board Regular
    Join Date
    Oct 2011
    Posts
    4,258
    Post Thanks / Like
    Mentioned
    12 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Date extraction

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

     AB
    1LAX Marketing Jan 17 True UpJan-17

    Spreadsheet Formulas
    CellFormula
    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. #3
    New Member
    Join Date
    May 2017
    Posts
    44
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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?
    Last edited by jeongs1; Jan 19th, 2018 at 11:32 AM.

  4. #4
    Board Regular
    Join Date
    Jan 2015
    Posts
    873
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #5
    New Member
    Join Date
    May 2017
    Posts
    44
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Date extraction

    Quote Originally Posted by admiral100 View Post
    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. #6
    Board Regular
    Join Date
    Jan 2015
    Posts
    873
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #7
    Board Regular
    Join Date
    Oct 2011
    Posts
    4,258
    Post Thanks / Like
    Mentioned
    12 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Date extraction

    Try this for end of month.

     AB
    1LAX Marketing Jan 17 True Up1/31/17

    Spreadsheet Formulas
    CellFormula
    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. #8
    New Member
    Join Date
    May 2017
    Posts
    44
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Date extraction

    Quote Originally Posted by AhoyNC View Post
    Try this for end of month.

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

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


    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?
    Last edited by jeongs1; Jan 19th, 2018 at 05:59 PM.

  9. #9
    Board Regular
    Join Date
    Oct 2011
    Posts
    4,258
    Post Thanks / Like
    Mentioned
    12 Post(s)
    Tagged
    1 Thread(s)

    Default 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.

     AB
    1LAX Marketing Jan 17 True Up1/31/2017
    2LAX Marketing True Up Through Feb 172/28/2017
    3LAX Marketing True Mar 17Up Through3/31/2017
    4Apr 17  LAX Marketing True Up Through4/30/2017

    Spreadsheet Formulas
    CellFormula
    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. #10
    Board Regular
    Join Date
    Jan 2015
    Posts
    873
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)

    Default 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

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •