Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Apr 2003
    Location
    Vail, Colorado, USA
    Posts
    194
    Thanks
    36
    Thanked 0 Times in 0 Posts
    I download english data into Excel that includes dates in the English format ddmmyyyy which I need to convert to the US format. As some of the dates appear to Excel to be correct they are accepted into Excel as dates the rest as text. First I turn all the dates into text then rebuild them as US dates. The whole procedure is cumbersome. I think I may not be using the find function correctly. Any help would be appreciated the two blocks of code follow.

    Thanks

    Mitch

    Sub FirstCConformDates() 'Tranfers from DDMMYYYY to MMDDYYYY
    Columns("C:G").Insert shift:=xlToRight 'Inserts columns for date conversion
    Cells(FR, 3).Select 'Selects first row of data
    For x = FR To LR 'Loops through each date to make sure it is in a text form so it can be converted to US format
    ActiveCell.FormulaR1C1 = "=IF(ISNONTEXT(RC[-1]),CONCATENATE(MONTH(RC[-1]),""/"",DAY(RC[-1]),""/"",YEAR(RC[-1])),RC[-1])"
    ActiveCell.Offset(1, 0).Select
    Next x
    Columns("C:C").Copy
    Columns("C:C").PasteSpecial Paste:=xlValues
    Columns("B:B").Delete
    End Sub

    Sub FirstCSplitDates() 'Arranges Dates to US form and also collects 1st and last dates for file name
    Cells(FR, 3).Select 'Selects first row of data
    For x = FR To LR
    ActiveCell.FormulaR1C1 = "=LEFT(RC[-1],FIND(""/"",RC[-1])-1)" 'Gets Day
    F2 = ActiveCell.Value
    ActiveCell.Offset(0, 1).Select 'Gets Month
    ActiveCell.FormulaR1C1 = "=MID(RC[-2],FIND(""/"",RC[-2])+1,FIND(""/"",RC[-2],LEN(RC[-1])+2)-(FIND(""/"",RC[-2]))-1)"
    f1 = ActiveCell.Value
    ActiveCell.Offset(0, 1).Select
    ActiveCell.FormulaR1C1 = "=RIGHT(RC[-3],4)" 'Gets Year
    F3 = ActiveCell.Value
    F4 = f1 & "-" & F2 & "-" & F3 'Sets the date in a format that can be saved
    If x = FR Then FstD = F4 'Gets the date if it is the first for part of the file name
    ActiveCell.Offset(0, 1).Select
    ActiveCell.FormulaR1C1 = "=CONCATENATE(RC[-2],""/"",RC[-3],""/"",RC[-1])" 'constructs date US
    ActiveCell.Offset(1, -3).Select 'Returns 1st col for next calculation
    Next x
    LstD = f1 & "-" & F2 & "-" & F3 'Gets the last date for part of the file name
    Columns("C:F").Select 'Selects Date working columns
    Selection.Copy
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False 'Copies values only
    Columns("B:E").Select 'Deletes working columns
    Selection.Delete
    End Sub

  2. #2
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts
    As long as your UK dates are not mixed in with US format dates, why not download the data into a worksheet that uses the UK format, then convert the dates into US format - using the General format (which converts the dates into an all number format) as an intermediary?
    Gre

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    It looks like the dates are in column B.
    It's not clear where FR and LR come from; I assume they are defined elsewhere.
    You could use this macro instead of the two you use now.

    Code:
    Sub Test()
      Dim arr
      For x = FR To LR
    	arr = Split(Cells(x, 2), "/")
    	Cells(x, 2) = DateSerial(arr(2), arr(1), arr(0))
      Next x
    End Sub

  4. #4
    2 Star Lounger
    Join Date
    Apr 2003
    Location
    Vail, Colorado, USA
    Posts
    194
    Thanks
    36
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='796799' date='07-Oct-2009 10:28']It looks like the dates are in column B.
    It's not clear where FR and LR come from; I assume they are defined elsewhere.
    You could use this macro instead of the two you use now.

    Code:
    Sub Test()
      Dim arr
      For x = FR To LR
    	arr = Split(Cells(x, 2), "/")
    	Cells(x, 2) = DateSerial(arr(2), arr(1), arr(0))
      Next x
    End Sub
    [/quote]

    Hans

    That is so simple thank you very much.

    Mitch

  5. #5
    2 Star Lounger
    Join Date
    Apr 2003
    Location
    Vail, Colorado, USA
    Posts
    194
    Thanks
    36
    Thanked 0 Times in 0 Posts
    [quote name='unkamunka' post='796798' date='07-Oct-2009 10:21']As long as your UK dates are not mixed in with US format dates, why not download the data into a worksheet that uses the UK format, then convert the dates into US format - using the General format (which converts the dates into an all number format) as an intermediary?[/quote]


    Thanks for the suggestion, but Hans solution is so simple that it works just fine for me. I Am converting the download into a iff file for Quick Books so there is a lot of other work do do.

    Mitch

Posting Permissions

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