Results 1 to 5 of 5
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Wrong date format (2000)

    I saved a file as a csv file. I have a macro that takes that csv file and converts it to Excel.
    For some reason the date is formatted 2004/1/1 12:00AM.
    I want to change the format to 1/1/2004 without the time but I can't seem to change it the conventional way highlighting the column and format, cells, number, date.
    Does anyone know how to get this field to change?
    Thanks for your help....

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Wrong date format (2000)

    Could you please post your macro code?
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Wrong date format (2000)

    Sub ImportMultSheets()
    Dim wks As Worksheet
    Dim sPathFilename As String
    Dim lRow As Long
    Dim lLimit As Long
    Dim sLine As String
    Dim sArray() As String

    sPathFilename = "C:JanetDOrnish_200_2.csv"
    lLimit = 65536
    ReDim sArray(1 To lLimit, 0)
    lRow = 1
    Open sPathFilename For Input As #1
    Do While Not EOF(1)
    Line Input #1, sLine
    sArray(lRow, 0) = sLine
    lRow = lRow + 1
    If lRow = lLimit + 1 Then
    Set wks = Worksheets.Add
    With wks
    .Range("A1").Resize(lLimit, 1).Value = sArray
    .Columns("a:a").TextToColumns _
    Destination:=Range("A1"), _
    DataType:=xlDelimited, Comma:=True
    End With
    ReDim sArray(1 To lLimit, 0)
    lRow = 1
    End If
    Loop
    Set wks = Worksheets.Add
    With wks
    .Range("A1").Resize(lLimit, 1).Value = sArray
    .Columns("a:a").TextToColumns _
    Destination:=Range("A1"), _
    DataType:=xlDelimited, Comma:=True
    End With
    Close #1
    Set wks = Nothing
    End Sub

  4. #4
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Wrong date format (2000)

    I would do this:

    1. run your macro until the text to columns bit
    2. Stop the macro and go to excel
    3. turn on the macro recorder
    4. do the text to columns manually and in one of the steps, make sure you select the column with the dates and choose the YMD date format.
    5. finish the texttocolumns wizard, stop recording the macro and check out what has been recorded.
    6. Post that here.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  5. #5
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Wrong date format (2000)

    Hi Linda

    I wrote this bit of code to insert in a module. I am assuming that the dates are in Column A.

    Sub ChangeFormat()
    Dim DateTime As Date
    Dim RowCount As Integer
    Dim List As Integer

    RowCount = Range("A65535").End(xlUp).Row
    Range("A1").Select

    For List = 1 To RowCount

    ActiveCell = Format(ActiveCell.Value, "dd/mm/yy")
    ActiveCell.Offset(1, 0).Select

    Next List

    End Sub

    I hope this helps.
    Jerry

Posting Permissions

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