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 fi...
  • HansVogelaar's avatar
    Oct 11, 2021

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

Resources