SOLVED

How to standardize dates

Brass Contributor

Good Morning
I need to make a macro to fix all the Date entries that are entered. Some dates are entered with 10/01/2021" others "01-10-2021" or "01.10.2021".
With this routine, when opening the file, all dates are reset to the slashes "/".
The routine works but is quite slow. Is there another way of faster programming to do?

Thank you for your help

Sub Formatar_Datas()
    Application.Calculation = xlCalculationManual
    Application.DisplayAlerts = False
    Application.ScreenUpdating = False
    Application.EnableEvents = False

    Dim Cell As Range
    Dim Cont As Long
    Dim ContBD As Integer
    
    Dim Arr1 As Variant
    Arr1 = Array("J4:J", "Q4:Q", "R4:R", "S4:S", "T4:T", "U4:U", "V4:V", "W4:W", "X4:X", "Y4:Y", "Z4:Z", "AA4:AA", "AG4:AG", "AI4:AI", "AK4:AK", "AT4:AT", "AV4:AV", "AW4:AW", "AZ4:AZ", "BB4:BB", "BD4:BD", "BM4:BM", "BO4:BO")

    ContBD = 1
    For Cont = LBound(Arr1) To UBound(Arr1)
        For Each Cell In Folha3.Range(Arr1(Cont) & Folha3.Cells(Folha3.Rows.Count, "C").End(xlUp).Row)
            If IsDate(Cell.Value) = False Then Cell.Value = ""
            If Len(Cell.Value) = 10 Then
                Cell.Value = Replace(Cell.Value, ".", "/")
                Cell.Value = Replace(Cell.Value, "-", "/")
                Cell.Value = Replace(Cell.Value, "_", "/")
            End If
        Next Cell
        ContBD = ContBD + 1
    Next Cont

    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
    Application.EnableEvents = True
End Sub
9 Replies
best response confirmed by csoares (Brass Contributor)
Solution

@csoares 

This should be faster:

 

Sub Formatar_Datas()
    Dim m As Long

    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Application.DisplayAlerts = False
    Application.Calculation = xlCalculationManual

    m = Folha3.Range("C" & Folha3.Rows.Count).End(xlUp).Row
    With Range("J4:J" & m & ",Q4:Q" & m & ",R4:R" & m & ",S4:S" & m & ",T4:T" & m & _
            ",U4:U" & m & ",V4:V" & m & ",W4:W" & m & ",X4:X" & m & ",Y4:Y" & m & _
            ",Z4:Z" & m & ",AA4:AA" & m & ",AG4:AG" & m & ",AI4:AI" & m & ",AK4:AK" & m & _
            ",AT4:AT" & m & ",AV4:AV" & m & ",AW4:AW" & m & ",AZ4:AZ" & m & _
            ",BB4:BB" & m & ",BD4:BD" & m & ",BM4:BM" & m & ",BO4:BO" & m)
        .Replace What:=".", Replacement:="/"
        .Replace What:="-", Replacement:="/"
        .Replace What:="_", Replacement:="/"
    End With

    'Application.Calculation = xlCalculationautomatic
    Application.DisplayAlerts = True
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub

 

But are all dates in ddmmyyyy order? Or all in mmddyyyy order? If there is a mixture, you have a problem...

It was almost instantaneous :-), I'll try to understand the changes and find out why it was super fast :)
Dates are all in dd/mm/yyyy format.
Thank you for your help

@csoares 

The code is so fast because it does not loop through the cells. It uses the Replace method of the range to act on all cells in one go.

@Hans Vogelaar A strange thing is happening, if you see in the attached image, the cell is formatted as Date "/", but in the visible part of the cell is the date with "-", but if we place the mouse on the cell, we can see that it is recorded as "/".
When running the code below, visually, the cell always has the "-".

Dim m As Long
m = Folha3.Range("C" & Folha3.Rows.Count).End(xlUp).Row
With Range("AT4:AT" & m)
    .Replace What:=".", Replacement:="/"
    .Replace What:="-", Replacement:="/"
    .Replace What:="_", Replacement:="/"
End With


Why is this happening?Captura de ecrã 2021-10-11 143401.jpg

@csoares Select from A4 down to the last used row,

Set the number format to dd/mm/yyyy (or its equivalent in Portuguese)

@csoares 

Playing with 365 insider beta, the following worksheet function creates dates.

=  --REDUCE(dates, {"-",".","_"}, LAMBDA(acc,s, SUBSTITUTE(acc,s,"/")))

returns serial numbers for the array 'dates' in any of the formats you mention.  Alternatively, one can introduce a named Lambda function

"REPLACEλ"
= LAMBDA(acc,s, SUBSTITUTE(acc, s, "/"))

"within the worksheet formula"
= --REDUCE(dates, {"-",".","_"}, REPLACEλ)

This is just 'for information'.  The solution you need was the VBA with the formula applied to the entire range.

Thanks for the explanation, I will need some study to understand the code, it's always good to learn.
Can I use the same method as With to erase the contents of all cells, in a given column, that do not start with the characters "PT"?
I can do it with a cycle, but I'm not seeing how I can do it faster. Thanks

@csoares 

Try this:

Sub EraseSomeText()
    With Range("E:E")
        .AutoFilter Field:=1, Criteria1:="<>PT*"
        .ClearContents
        .AutoFilter
    End With
End Sub

Change E:E to the relevant column.

1 best response

Accepted Solutions
best response confirmed by csoares (Brass Contributor)
Solution

@csoares 

This should be faster:

 

Sub Formatar_Datas()
    Dim m As Long

    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Application.DisplayAlerts = False
    Application.Calculation = xlCalculationManual

    m = Folha3.Range("C" & Folha3.Rows.Count).End(xlUp).Row
    With Range("J4:J" & m & ",Q4:Q" & m & ",R4:R" & m & ",S4:S" & m & ",T4:T" & m & _
            ",U4:U" & m & ",V4:V" & m & ",W4:W" & m & ",X4:X" & m & ",Y4:Y" & m & _
            ",Z4:Z" & m & ",AA4:AA" & m & ",AG4:AG" & m & ",AI4:AI" & m & ",AK4:AK" & m & _
            ",AT4:AT" & m & ",AV4:AV" & m & ",AW4:AW" & m & ",AZ4:AZ" & m & _
            ",BB4:BB" & m & ",BD4:BD" & m & ",BM4:BM" & m & ",BO4:BO" & m)
        .Replace What:=".", Replacement:="/"
        .Replace What:="-", Replacement:="/"
        .Replace What:="_", Replacement:="/"
    End With

    'Application.Calculation = xlCalculationautomatic
    Application.DisplayAlerts = True
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub

 

But are all dates in ddmmyyyy order? Or all in mmddyyyy order? If there is a mixture, you have a problem...

View solution in original post