Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    Nov 2001
    Posts
    19
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Replace a stringed date into date (Excel 2002 SP3)

    I am trying to import into Excel, a column from a text file, which contains a date in the format "dd/mm/yyyy1". The number 1 is attached to the year, thus I am getting a string and not a date.

    I am looking for a macro that will replace this string to a date with the "dd/mm/yyyy" format. The day and month shouldn't change, and the year should change from 20061 to 2006.

    Thank you in advance.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Replace a stringed date into date (Excel 2002 SP3)

    You can do it wiith formulas: say that the date strings are in A1:A100.
    If necessary, insert a blank column in column B.
    In B1, enter the formula
    <code>
    =DATE(MID(A1,7,4),MID(A1,4,2),LEFT(A1,2))
    </code>
    or
    <code>
    =DATEVALUE(LEFT(A1,LEN(A1)-1))
    </code>
    and fill down to B100. In the second example, you may have to apply date format explicitly.
    You can now select B1:B100, copy to the clipboard, then Edit | Paste Special with the Values option, to get rid of the formulas.
    Column A can be deleted now, it is no longer necessary.

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Replace a stringed date into date (Excel 2002 SP3)

    If you prefer a macro:

    Sub ConvertDates()
    ' Modify the constants as needed
    Const strCol = "A"
    Const lngMinRow = 1

    Dim lngRow As Long
    Dim lngMaxRow As Long
    Dim strVal As String

    lngMaxRow = Range(strCol & 65536).End(xlUp).Row
    For lngRow = lngMinRow To lngMaxRow
    strVal = Range(strCol & lngRow)
    Range(strCol & lngRow) = DateSerial(Mid(strVal, 7, 4), _
    Mid(strVal, 4, 2), Left(strVal, 2))
    Next lngRow
    End Sub

  4. #4
    New Lounger
    Join Date
    Nov 2001
    Posts
    19
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Replace a stringed date into date (Excel 2002 SP3)

    Dear Hans,
    I knew how to do it with a formula, but not with a macro.
    Your macro is doing the job perfectly.
    Thank you very much.

    Avi

Posting Permissions

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