1

I'm collecting data from many workbooks. The problem is, in every document the date format is not consistent: 16/01/2015 2015-01-06 24.03.2014

What I want to achieve is "YYYY-MM-DD" format. In my code I've a case which responsible to clear the date columns. Now I'm getting desperate and I added stupid amount of date formatting to the code, but no changes when it comes to the dotted format (one with bold above). Even If I select manually the column and change the format type, or copy the values to a .txt file then copy back to the original sheet, also tried with a new WorkBook, but nothing happened. Why is it impossible to change the date values in this few instances? Any help would be appreciated. Here is the code:

        Case 6:

    sourceWorkbook.Activate
    sourceWS.Activate

    sourceWS.Columns(i).Select
    Selection.NumberFormat = "YYYY-MM-DD;@"
    sourceWS.Range(Cells(2, i), Cells(lastrw, i)).NumberFormat = "YYYY-MM-DD;@"


        For j = startRow To lastrw Step 1
            'Assign the header to the first row
            NewWorksheet.Cells(1, i) = sourceWS.Cells(startRow, i).Value

            On Error Resume Next
                textToFormat = CStr(sourceWS.Cells(j, i).Value)

                d = CDate(textToFormat)
                finalDate = Format(textToFormat, "YYYY-MM-DD")

                NewWorksheet.Cells(j - adjustRows, i) = finalDate
                'This error handler purpose to handle the header name!
                If Err Then

                    NewWorksheet.Cells(j - adjustRows, i) = textToFormat

                End If

            On Error GoTo 0

        Next j
        Set fckFormat = NewWorksheet.Columns(i)
        fckFormat.NumberFormat = "YYYY-MM-DD;@"
        NewBook.Activate
        NewWorksheet.Activate
        NewWorksheet.Columns(i).Select
        Selection.NumberFormat = "YYYY-MM-DD;@"
R3uK
  • 14,417
  • 7
  • 43
  • 77
simpleMan
  • 55
  • 1
  • 1
  • 8
  • Possible duplicate of [Too Many Different Cell Formats](http://stackoverflow.com/questions/2449755/too-many-different-cell-formats) – R3uK Feb 14 '17 at 10:41
  • See answer that start by : **Lots of people seem to run into this problem.** – R3uK Feb 14 '17 at 10:41
  • @R3uK , you mean in the source sheet or in the code? I tested the code it works for every other formats, except the mentioned one. – simpleMan Feb 14 '17 at 10:55
  • 2
    The cells that hold the date values in `24.03.2014` format, are they formatted as dates or strings? – Jordan Feb 14 '17 at 10:57
  • +1 for @Jordan question! Does changing manually the format affect the content displayed in a date cell? And I was suggest the utility that you can find here : https://onedrive.live.com/?authkey=%21AArEUjrx0NkTCUM&id=53E1D37F76F69444%21526&cid=53E1D37F76F69444 – R3uK Feb 14 '17 at 11:10
  • Do a find/replace on all columns affected, swapping `.` for `/`. – CLR Feb 14 '17 at 11:11
  • If all of the different dates are stored as dates in the cells (and not strings) you can simply use `.Value2` to extract the date as a `Double` and then use `Format(value, "yyyy-mm-dd")` to convert them to your desired format. – Jordan Feb 14 '17 at 11:15
  • http://www.ozgrid.com/VBA/convert-to-date.htm – John Muggins Feb 14 '17 at 14:30

1 Answers1

0

As Jordan suggested, the issue is that this data is formatted as a string rather than a date. For example if I enter each of the dates above in a new workbook, the first two are correctly detected as dates (and can be formatted as such) while the last one is treated as a text string. This may vary based on your regional settings.

Unfortunately, the CDate function is also failing to recognise this format as a valid date. Try running the following, and you'll get a Type mismatch error:

MsgBox CDate("24.03.2016")

You can use the Replace Method to convert this in to a valid date, by replacing the dot with a slash:

Set datecol = Sheets(1).Columns(1) 
datecol.Replace What:=".", Replacement:="/", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False

The cell will still be formatted as a string, but you can now use your existing loop to convert it to a date using CDate:

            d = CDate(textToFormat)
            NewWorksheet.Cells(j - adjustRows, i) = d

The NumberFormat will then work correctly.

Note: I would suggest using CDate rather than Format so that the date is still stored as a value rather than text.

Jane
  • 851
  • 4
  • 9