Consulting

Results 1 to 7 of 7

Thread: Solved: Problems formatting different date formats

  1. #1
    VBAX Contributor
    Joined
    Apr 2008
    Posts
    136
    Location

    Solved: Problems formatting different date formats

    hello,

    Ive tried a number of ways and different codes, but nothing seems to work.

    I am trying to format different date formats in a column so that they are ALL uniform i.e. dd/mm/yyyy (english, united kingdom)

    I even recorded a macro and this is the code


    [vba]
    Columns("A:A").Select
    Selection.Copy
    Range("C1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlAdd, SkipBlanks _
    :=False, Transpose:=False
    Application.CutCopyMode = False
    Selection.NumberFormat = "dd/mm/yyyy;@"

    [/vba]

    sample dates are:
    12/05/08 12:23:06
    05/05/2008 13:14:08
    10-May-08
    13/05/08
    09-May-2008

    Please note: some date will have times with them. The xl. sheet needs to be converted to a csv file after

    Please can someone help?

    Many Thanks.

  2. #2
    Distinguished Lord of VBAX VBAX Grand Master xld's Avatar
    Joined
    Apr 2005
    Posts
    25,147
    Location
    Maybe they are not real dates.

    See if this works any better

    [vba]

    With Columns("A:A")

    .TextToColumns Destination:=.Cells(1, 1), _
    DataType:=xlFixedWidth, _
    FieldInfo:=Array(Array(0, 4), Array(10, 1)), _
    TrailingMinusNumbers:=True
    .Copy
    Range("C1").PasteSpecial Paste:=xlPasteValues
    End With
    Columns("C:C").NumberFormat = "dd/mm/yyyy;@"
    [/vba]
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  3. #3
    VBAX Contributor
    Joined
    Apr 2008
    Posts
    136
    Location
    Thanks, kinda works. But now its swapping the months and days around. At least the Year is not changing to something funny like it was before! thanks

  4. #4
    Distinguished Lord of VBAX VBAX Grand Master xld's Avatar
    Joined
    Apr 2005
    Posts
    25,147
    Location
    Are the dates valid but just formatted US style, or does it change 10th Sep to 9th Oct for example?
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  5. #5
    VBAX Contributor
    Joined
    Apr 2008
    Posts
    136
    Location
    Valid dates. After code is run it goes from 12/3/2008 to 03/12/2008. Only the ones with the times mind you. Rest is ok. Should stay as 12/03/2008

  6. #6
    Distinguished Lord of VBAX VBAX Grand Master xld's Avatar
    Joined
    Apr 2005
    Posts
    25,147
    Location
    Maybe this

    [vba]

    Dim i As Long
    Columns("A").Copy Range("C1")
    Columns("C").TextToColumns Destination:=Range("C1"), _
    DataType:=xlFixedWidth, _
    FieldInfo:=Array(Array(0, 4), Array(10, 1)), _
    TrailingMinusNumbers:=True
    For i = 1 To Cells(Rows.Count, "C").End(xlUp).Row
    With Cells(i, "C")
    .Value = CDate(Cells(i, "C").Value \ 1)
    .NumberFormat = "dd/mm/yyyy"
    End With
    Next i
    [/vba]
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  7. #7
    VBAX Contributor
    Joined
    Apr 2008
    Posts
    136
    Location
    I turned all dates into an absolute number. i.e. =ABS(cell) then applied the format dd/mm/yyyy;@ to it. This seems to work for any date format. Thanks for your effort xld

Posting Permissions

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