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

Converting date to different format.

This is a discussion on Converting date to different format. within the Excel Questions forums, part of the Question Forums category; Hey, all. I receive a spreadsheet each day with up to 1000 rows. In two of these columns I am ...

  1. #1
    Board Regular
    Join Date
    Mar 2003
    Posts
    105

    Default Converting date to different format.

    Hey, all.

    I receive a spreadsheet each day with up to 1000 rows. In two of these columns I am provided a date in a text formatted column with the following format:

    Code:
    May 31 2007 12:00AM
    However, I need to put the date in MM/DD/YY format. I have tried my usual splitting, concatenate, etc... but I think due in part of it being a text based column in the first place, it is just now working. I am having to split them all, concatenate them, and then manually replace "May" with "5" and do that for each month. Obviously this is tedious and time consuming.

    Does anyone have any suggestions on how I can get to formatting this in a way that I could add into a macro?

    Thanks!

  2. #2
    MrExcel MVP Jonmo1's Avatar
    Join Date
    Oct 2006
    Location
    Bryan, TX
    Posts
    42,171

    Default Re: Converting date to different format.

    Try
    =SUBSTITUTE(LEFT(A1,LEN(A1)-8)," ",", ",2)+0
    Add-in for posting ranges as copyable tables (bottom of the page) -Win & Mac

    The more we learn, and the better we get at our trade, the easier it becomes to overlook the obvious.

    Life moves pretty fast. If you don't stop and look around once in a while, you could miss it.
    Ferris Bueller A.K.A. John Hughes, 1986

  3. #3
    Board Regular
    Join Date
    Mar 2003
    Posts
    105

    Default Re: Converting date to different format.

    I get the #VALUE! error when I input that formula. When I remove the "+0" from the end it returns the date in, "May 312007" format. If I add a space in the second set of quotes then I am given, "May 31 2007". Could I not do this through =left(A1,11)?

    I'm still trying to get it into MM/DD/YY format.

  4. #4
    MrExcel MVP
    Join Date
    May 2009
    Posts
    13,246

    Default Re: Converting date to different format.

    Here's a UDF you can try. Cell with formula in it is formatted as mm/dd/yy.
    Sheet6

     AB
    2May 31 2007 12:00AM05/31/07

    Spreadsheet Formulas
    CellFormula
    B2=ConvertToDate(A2)


    Excel tables to the web >> Excel Jeanie HTML 4

    Code:
    Function ConvertToDate(S As String)
    Dim V As Variant, M As Variant, D As Variant, Y As Variant
    V = Split(S, " ")
    M = V(0)
    For i = 1 To 12
        If MonthName(i) = M Then
            M = i
            Exit For
        End If
    Next i
    D = V(1)
    Y = V(2)
    ConvertToDate = CDate(M & "/" & D & "/" & Y)
    End Function
    Joe

    When I was a young man I knew everything. Now that I'm older, I realize I know very little, and what I do know, I tend to forget!

  5. #5
    Board Regular
    Join Date
    Mar 2012
    Posts
    126

    Default Re: Converting date to different format.

    Dear


    In addition to previous equation by JonMol =SUBSTITUTE(LEFT(A1,LEN(A1)-8)," ",", ",2)+0

    you can make it as follow:



    Code:
    =MONTH(TEXT(LEFT((SUBSTITUTE(LEFT(A1,LEN(A1)-8)," ",", ",2)+0),3),"m"))
    Quote Originally Posted by JokerFMJ View Post
    Hey, all.

    I receive a spreadsheet each day with up to 1000 rows. In two of these columns I am provided a date in a text formatted column with the following format:

    Code:
    May 31 2007 12:00AM
    However, I need to put the date in MM/DD/YY format. I have tried my usual splitting, concatenate, etc... but I think due in part of it being a text based column in the first place, it is just now working. I am having to split them all, concatenate them, and then manually replace "May" with "5" and do that for each month. Obviously this is tedious and time consuming.

    Does anyone have any suggestions on how I can get to formatting this in a way that I could add into a macro?

    Thanks!

  6. #6
    MrExcel MVP Jonmo1's Avatar
    Join Date
    Oct 2006
    Location
    Bryan, TX
    Posts
    42,171

    Default Re: Converting date to different format.

    Make sure you got the formula copied exactly, it works for me..

    Excel 2010
    ABC
    1May 31 2007 12:00AM5/31/2007May 31, 2007

    Sheet1



    Worksheet Formulas
    CellFormula
    B1=SUBSTITUTE(LEFT(A1,LEN(A1)-8)," ",", ",2)+0
    C1=SUBSTITUTE(LEFT(A1,LEN(A1)-8)," ",", ",2)

    Add-in for posting ranges as copyable tables (bottom of the page) -Win & Mac

    The more we learn, and the better we get at our trade, the easier it becomes to overlook the obvious.

    Life moves pretty fast. If you don't stop and look around once in a while, you could miss it.
    Ferris Bueller A.K.A. John Hughes, 1986

  7. #7
    Board Regular
    Join Date
    Jan 2015
    Posts
    488

    Default Re: Converting date to different format.

    another way

    =MID(A13,FIND(" ",A13),3)&"/"&MONTH((LEFT(A13,FIND(" ",A13)-1)&" 0"))&"/"&MID(A13,FIND("@",SUBSTITUTE(A13," ","@",2)&"@")+1,5)

    OR

    =MONTH((LEFT(A13,FIND(" ",A13)-1)&" 0"))&"/"&MID(A13,FIND(" ",A13)+1,2)&"/"&MID(A13,FIND("@",SUBSTITUTE(A13," ","@",2)&"@")+1,5)
    Last edited by admiral100; Dec 8th, 2015 at 01:59 PM.

  8. #8
    Board Regular
    Join Date
    Mar 2003
    Posts
    105

    Default Re: Converting date to different format.

    It does work for me, Jonmo. I'm not sure if I missed something before or what, but it's working. Thank you!

    Quote Originally Posted by Jonmo1 View Post
    Make sure you got the formula copied exactly, it works for me..

  9. #9
    MrExcel MVP Jonmo1's Avatar
    Join Date
    Oct 2006
    Location
    Bryan, TX
    Posts
    42,171

    Default Re: Converting date to different format.

    You're welcome.
    Add-in for posting ranges as copyable tables (bottom of the page) -Win & Mac

    The more we learn, and the better we get at our trade, the easier it becomes to overlook the obvious.

    Life moves pretty fast. If you don't stop and look around once in a while, you could miss it.
    Ferris Bueller A.K.A. John Hughes, 1986

  10. #10
    Board Regular
    Join Date
    Mar 2003
    Posts
    105

    Default Re: Converting date to different format.

    Joemo and Jonmo, both of your suggestions work great with one exception. When the day is a single digit (for example, August 8th), it comes across as:

    Code:
    AUG  8 2013 12:00AM
    This is causing the both suggestions to return a #VALUE error. Is there a way to incorporate a fix in this or will I just have to replace all double spaces with single spaces?

Page 1 of 2 12 LastLast

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
  •  


DMCA.com