Results 1 to 6 of 6

Thread: Help converting weird date format

  1. #1
    New Member
    Join Date
    Sep 2011
    Posts
    10
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Help converting weird date format

    I've got a file with dates entered as year-month:

    15-May
    16-Mar
    7-Jun

    These are meant to represent

    5/1/2015
    3/1/2016
    6/1/2007

    Unfortunately, they were saved in a date format that makes Excel see them as being a month and day in 2019:

    5/15/2019
    3/16/2019
    6/7/2019

    How can I convert them into the correct date format (15-May should be 5/1/2015, as I have only month and year for each value)?

    Thanks!!!

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

    Default Re: Help converting weird date format

    Assuming all of your dates are screwed up this way, give this macro a try. Note that I assumed your dates are located in Column A starting on Row 1... if this assumption is incorrect, then change the red text to the appropriate values.
    Code:
    Sub FixDates()
      Dim Col As String, StartRow As Long, LastRow As Long, R As Long
      Col = "A"
      StartRow = 1
      LastRow = Cells(Rows.Count, Col).End(xlUp).Row
      For R = StartRow To LastRow
        Cells(R, Col).Value = DateSerial(Day(Cells(R, Col)), Month(Cells(R, Col)), 1)
      Next
    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
    MrExcel MVP shg's Avatar
    Join Date
    May 2008
    Location
    The Great State of Texas
    Posts
    21,612
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    14 Thread(s)

    Default Re: Help converting weird date format

    Messy ...

    A
    B
    C
    1
    15-May
    01 May 2015
    B1: =--(1 & MID(A1, FIND("-", A1) + 1, 4) & LEFT(A1, FIND("-", A1) - 1))
    2
    16-Mar
    01 Mar 2016
    3
    7-Jun
    01 Jun 2007

  4. #4
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    6,257
    Post Thanks / Like
    Mentioned
    71 Post(s)
    Tagged
    14 Thread(s)

    Default Re: Help converting weird date format

    Try this

     AB
    1ActualResult
    205/15/201901/05/2015
    303/16/201901/03/2016
    406/07/201901/06/2007

    Formeln der Tabelle
    ZelleFormel
    B2=DATE("20"&TEXT(A2,"dd"),MONTH(A2),1)
    Regards Dante Amor

  5. #5
    New Member
    Join Date
    Sep 2011
    Posts
    10
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Help converting weird date format

    DanteAmor's solution worked perfectly! Thanks to everyone, but the challenge was that the dates were actually in serial format with the wrong date. Rebuilding it with the date function did the trick!

  6. #6
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    6,257
    Post Thanks / Like
    Mentioned
    71 Post(s)
    Tagged
    14 Thread(s)

    Default Re: Help converting weird date format

    Quote Originally Posted by petersw View Post
    DanteAmor's solution worked perfectly! Thanks to everyone, but the challenge was that the dates were actually in serial format with the wrong date. Rebuilding it with the date function did the trick!
    I'm glad to help you. Thanks for the feedback.
    Regards Dante Amor

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
  •