Forum Discussion

csoares's avatar
csoares
Brass Contributor
Oct 11, 2021
Solved

How to standardize dates

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
  • 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...

9 Replies

  • 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.

    • csoares's avatar
      csoares
      Brass Contributor
      Thanks for the explanation, I will need some study to understand the code, it's always good to learn.
  • 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...

    • csoares's avatar
      csoares
      Brass Contributor
      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.

Resources