Thread: Macro and date formating

HI,

I have a very stressful problem:

In my document, I have dates in the format dd.mm.yyyy and I need to change them to yyyy-mm-dd

problem is excel dosen't recognize the dot. I wrote the following macro to try to do the change:

    Cells.Replace What:=".", Replacement:="/", LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
        
    Dim h As Integer
    For h = 1 To nbr_of_rows
        Cells(h, 11).Select
        ActiveCell.FormulaR1C1 = "=RC[-7]+RC[1]"
        Cells(h, 4).Select
        ActiveCell.FormulaR1C1 = Cells(h, 11).Value
    Next h
    
        Columns("D:D").Select
        Selection.NumberFormat = "yyyy-mm-dd"

The cumbersome addition and re-pluggin of value in the same cell is because otherwise excel would read the content of the cells and wouldn't apply the formating to it.
that works for the dates with days above 13. Otherwise it recognizes the day as a month...

__this______ becomes______that______
13.09.2011 ____________ 2011-09-13
13.09.2011 ____________ 2011-09-13
12.09.2011 ____________ 2011-12-09
12.09.2011 ____________ 2011-12-09

What's wrong ?

You could try using Find & Replace

Select your range of dates then
Find what:= "." (dot without the quotes)
Replace with:= "/" (without the quotes)
Replace All

or this formula, where your dates are in A2 down.

=SUBSTITUTE(A2,".","/")*1

Drag/Fill Down

Then format the cells as you wish

Dates can be fickle in VBa
Try this, assuming your dates are in A2 down.

Option Explicit

Sub ConvertToDate()
    Dim LastRow As Long
    Dim Cell As Range
    
    LastRow = Range("A" & Rows.Count).End(xlUp).Row
    For Each Cell In Range("A2:A" & LastRow)
        With Cell
        .Replace What:=".", Replacement:="/"
        .Value = CDate(.Value)
        .NumberFormat = "yyyy-mm-dd"
        End With
    Next
End Sub