Results 1 to 6 of 6
  1. #1
    4 Star Lounger
    Join Date
    May 2003
    Location
    Manchester, Gtr Manchester, England
    Posts
    552
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I have a number of columns where the date has been entered in any number of formats, the data is formatted to just general.simply to changing today doesnt work. What I would like is for the date to be shown as 01/05/09 where this is 1st May 2009. Is there a routine or bit of code or a.n. other technique I can us to change the records in my columns?


    Thanks, Darren.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Could you post a small sample workbook? Thanks in advance.

  3. #3
    4 Star Lounger
    Join Date
    May 2003
    Location
    Manchester, Gtr Manchester, England
    Posts
    552
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='772855' date='29-Apr-2009 12:08']Could you post a small sample workbook? Thanks in advance.[/quote]


    Hi Hans, columns E f I and J

    Thanks, Darren.
    Attached Files Attached Files

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Here is a query you can use:

    Code:
    Sub Convert2Date()
      Dim c As Variant
      Dim oCell As Range
      Application.ScreenUpdating = False
    	For Each oCell In Range("E:E,F:F,I:I,J:J").SpecialCells(xlCellTypeConstants, xlTextValues)
    	  If oCell.Row > 1 Then
    		oCell = DateSerial(2000 + Right(oCell, 2), Mid(oCell, 4, 2), Left(oCell, 2))
    	  End If
    	Next oCell
      Application.ScreenUpdating = True
    End Sub
    Note: you don't have to worry about the date format. You can set the Format property in Access after importing the tables.

  5. #5
    4 Star Lounger
    Join Date
    May 2003
    Location
    Manchester, Gtr Manchester, England
    Posts
    552
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='772862' date='29-Apr-2009 13:04']Here is a query you can use:

    Code:
    Sub Convert2Date()
      Dim c As Variant
      Dim oCell As Range
      Application.ScreenUpdating = False
    	For Each oCell In Range("E:E,F:F,I:I,J:J").SpecialCells(xlCellTypeConstants, xlTextValues)
    	  If oCell.Row > 1 Then
    		oCell = DateSerial(2000 + Right(oCell, 2), Mid(oCell, 4, 2), Left(oCell, 2))
    	  End If
    	Next oCell
      Application.ScreenUpdating = True
    End Sub
    Note: you don't have to worry about the date format. You can set the Format property in Access after importing the tables.[/quote]

    Cheers, Hans, that is marvellous.

    Darren

  6. #6
    2 Star Lounger
    Join Date
    Jan 2002
    Location
    Tampico, Tamps, Mexico
    Posts
    118
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Another way without using code:

    Advantage: You do not need any code
    Disadvantage: You need to make this column by column.

    1. Select the range (only one column) by example: [I2:I101]
    2. Menu Data | Text to Column
    3. Step 1 of 3 - Fixed width | Next
    4. Step 2 of 3 |Next
    5. Step 3 of 4 select radial button Date “DMY” | OK/Finish

Posting Permissions

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