join a list of date
Results 1 to 7 of 7

Thread: join a list of date
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Aug 2019
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default join a list of date

    I want to join a list of date together quickly. the join function requires the text function for every single date.
    Is there an easier way to join a list of date together?

  2. #2
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    35,085
    Post Thanks / Like
    Mentioned
    92 Post(s)
    Tagged
    33 Thread(s)

    Default Re: join a list of date

    You have left some important information out of your post... where is the list of dates? how do you want them joined? where do you want the output to go to?

    Just guessing here... I'll assume your dates are in cells A1:A10, that your want the output to be m/d/yyyy forma, that you want them joined using a comma/space as the delimiter and that you want the output to be shown in a MessageBox... here is a macro that will do this
    Code:
    Sub DateLister
      MsgBox Join(Application.Transpose(Evaluate("IF({1},TEXT(A1:A10,""m/d/yyyy""))")), ", ")
    End Sub
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  3. #3
    New Member
    Join Date
    Aug 2019
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: join a list of date

    Is there away to do it without using VBA?

  4. #4
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    35,085
    Post Thanks / Like
    Mentioned
    92 Post(s)
    Tagged
    33 Thread(s)

    Default Re: join a list of date

    Quote Originally Posted by linkn00 View Post
    Is there away to do it without using VBA?
    I am not sure... my version of Excel is 2010... some of the later versions have a function called TEXTJOIN which I think would work the same way I used Join (except you should not have to TRANSPOSE the array result), but I am not sure and have no way to test it. If your version of Excel has this function, then I think the answer is "yes" and if you can figure it out yourself, great; otherwise you will have to wait for someone to come along who has that function in their version of Excel in order to show you the way.
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  5. #5
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    40,386
    Post Thanks / Like
    Mentioned
    86 Post(s)
    Tagged
    19 Thread(s)

    Default Re: join a list of date

    Welcome to the MrExcel board!

    Rick pointed out that you have omitted some information from your post and you still have not provided that information.
    Quote Originally Posted by Rick Rothstein View Post
    You have left some important information out of your post... where is the list of dates? how do you want them joined? where do you want the output to go to?
    You also mentioned the "join function" which is a function in vba, not worksheet functions, so it is not surprising that a vba suggestion was made.

    So, still guessing at some of the things Rick asked, see if this formula helps. It is an array formula so should be entered without the {} but confirmed with Ctrl+Shift+Enter, not just Enter. If confirmed correctly, Excel will insert the {}.

    Join dates

    BC
    12-May-182-May-18, 25-Dec-17, 15-Oct-19
    225-Dec-17
    315-Oct-19

    Spreadsheet Formulas
    CellFormula
    C1{=TEXTJOIN(", ",TRUE,TEXT(B1:B3,"d-mmm-yy"))}
    Formula Array:
    Produce enclosing
    { } by entering
    formula with CTRL+SHIFT+ENTER!


    Excel tables to the web >> Excel Jeanie HTML 4
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the VBHTML Maker
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

  6. #6
    New Member
    Join Date
    Aug 2019
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: join a list of date

    Thanks so much, exactly what i am looking for

  7. #7
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    40,386
    Post Thanks / Like
    Mentioned
    86 Post(s)
    Tagged
    19 Thread(s)

    Default Re: join a list of date

    Quote Originally Posted by linkn00 View Post
    Thanks so much, exactly what i am looking for
    You're welcome. Thanks for letting us know.
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the VBHTML Maker
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

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
  •