SOLVED

How to standardize dates

%3CLINGO-SUB%20id%3D%22lingo-sub-2833437%22%20slang%3D%22en-US%22%3EHow%20to%20standardize%20dates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2833437%22%20slang%3D%22en-US%22%3E%3CP%3EGood%20Morning%3CBR%20%2F%3EI%20need%20to%20make%20a%20macro%20to%20fix%20all%20the%20Date%20entries%20that%20are%20entered.%20Some%20dates%20are%20entered%20with%2010%2F01%2F2021%22%20others%20%2201-10-2021%22%20or%20%2201.10.2021%22.%3CBR%20%2F%3EWith%20this%20routine%2C%20when%20opening%20the%20file%2C%20all%20dates%20are%20reset%20to%20the%20slashes%20%22%2F%22.%3CBR%20%2F%3EThe%20routine%20works%20but%20is%20quite%20slow.%20Is%20there%20another%20way%20of%20faster%20programming%20to%20do%3F%3C%2FP%3E%3CP%3EThank%20you%20for%20your%20help%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-visual-basic%22%3E%3CCODE%3ESub%20Formatar_Datas()%0A%20%20%20%20Application.Calculation%20%3D%20xlCalculationManual%0A%20%20%20%20Application.DisplayAlerts%20%3D%20False%0A%20%20%20%20Application.ScreenUpdating%20%3D%20False%0A%20%20%20%20Application.EnableEvents%20%3D%20False%0A%0A%20%20%20%20Dim%20Cell%20As%20Range%0A%20%20%20%20Dim%20Cont%20As%20Long%0A%20%20%20%20Dim%20ContBD%20As%20Integer%0A%20%20%20%20%0A%20%20%20%20Dim%20Arr1%20As%20Variant%0A%20%20%20%20Arr1%20%3D%20Array(%22J4%3AJ%22%2C%20%22Q4%3AQ%22%2C%20%22R4%3AR%22%2C%20%22S4%3AS%22%2C%20%22T4%3AT%22%2C%20%22U4%3AU%22%2C%20%22V4%3AV%22%2C%20%22W4%3AW%22%2C%20%22X4%3AX%22%2C%20%22Y4%3AY%22%2C%20%22Z4%3AZ%22%2C%20%22AA4%3AAA%22%2C%20%22AG4%3AAG%22%2C%20%22AI4%3AAI%22%2C%20%22AK4%3AAK%22%2C%20%22AT4%3AAT%22%2C%20%22AV4%3AAV%22%2C%20%22AW4%3AAW%22%2C%20%22AZ4%3AAZ%22%2C%20%22BB4%3ABB%22%2C%20%22BD4%3ABD%22%2C%20%22BM4%3ABM%22%2C%20%22BO4%3ABO%22)%0A%0A%20%20%20%20ContBD%20%3D%201%0A%20%20%20%20For%20Cont%20%3D%20LBound(Arr1)%20To%20UBound(Arr1)%0A%20%20%20%20%20%20%20%20For%20Each%20Cell%20In%20Folha3.Range(Arr1(Cont)%20%26amp%3B%20Folha3.Cells(Folha3.Rows.Count%2C%20%22C%22).End(xlUp).Row)%0A%20%20%20%20%20%20%20%20%20%20%20%20If%20IsDate(Cell.Value)%20%3D%20False%20Then%20Cell.Value%20%3D%20%22%22%0A%20%20%20%20%20%20%20%20%20%20%20%20If%20Len(Cell.Value)%20%3D%2010%20Then%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20Cell.Value%20%3D%20Replace(Cell.Value%2C%20%22.%22%2C%20%22%2F%22)%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20Cell.Value%20%3D%20Replace(Cell.Value%2C%20%22-%22%2C%20%22%2F%22)%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20Cell.Value%20%3D%20Replace(Cell.Value%2C%20%22_%22%2C%20%22%2F%22)%0A%20%20%20%20%20%20%20%20%20%20%20%20End%20If%0A%20%20%20%20%20%20%20%20Next%20Cell%0A%20%20%20%20%20%20%20%20ContBD%20%3D%20ContBD%20%2B%201%0A%20%20%20%20Next%20Cont%0A%0A%20%20%20%20Application.DisplayAlerts%20%3D%20True%0A%20%20%20%20Application.ScreenUpdating%20%3D%20True%0A%20%20%20%20Application.EnableEvents%20%3D%20True%0AEnd%20Sub%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2833437%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2833549%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20standardize%20dates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2833549%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1084899%22%20target%3D%22_blank%22%3E%40csoares%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThis%20should%20be%20faster%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3ESub%20Formatar_Datas()%0A%20%20%20%20Dim%20m%20As%20Long%0A%0A%20%20%20%20Application.ScreenUpdating%20%3D%20False%0A%20%20%20%20Application.EnableEvents%20%3D%20False%0A%20%20%20%20Application.DisplayAlerts%20%3D%20False%0A%20%20%20%20Application.Calculation%20%3D%20xlCalculationManual%0A%0A%20%20%20%20m%20%3D%20Folha3.Range(%22C%22%20%26amp%3B%20Folha3.Rows.Count).End(xlUp).Row%0A%20%20%20%20With%20Range(%22J4%3AJ%22%20%26amp%3B%20m%20%26amp%3B%20%22%2CQ4%3AQ%22%20%26amp%3B%20m%20%26amp%3B%20%22%2CR4%3AR%22%20%26amp%3B%20m%20%26amp%3B%20%22%2CS4%3AS%22%20%26amp%3B%20m%20%26amp%3B%20%22%2CT4%3AT%22%20%26amp%3B%20m%20%26amp%3B%20_%0A%20%20%20%20%20%20%20%20%20%20%20%20%22%2CU4%3AU%22%20%26amp%3B%20m%20%26amp%3B%20%22%2CV4%3AV%22%20%26amp%3B%20m%20%26amp%3B%20%22%2CW4%3AW%22%20%26amp%3B%20m%20%26amp%3B%20%22%2CX4%3AX%22%20%26amp%3B%20m%20%26amp%3B%20%22%2CY4%3AY%22%20%26amp%3B%20m%20%26amp%3B%20_%0A%20%20%20%20%20%20%20%20%20%20%20%20%22%2CZ4%3AZ%22%20%26amp%3B%20m%20%26amp%3B%20%22%2CAA4%3AAA%22%20%26amp%3B%20m%20%26amp%3B%20%22%2CAG4%3AAG%22%20%26amp%3B%20m%20%26amp%3B%20%22%2CAI4%3AAI%22%20%26amp%3B%20m%20%26amp%3B%20%22%2CAK4%3AAK%22%20%26amp%3B%20m%20%26amp%3B%20_%0A%20%20%20%20%20%20%20%20%20%20%20%20%22%2CAT4%3AAT%22%20%26amp%3B%20m%20%26amp%3B%20%22%2CAV4%3AAV%22%20%26amp%3B%20m%20%26amp%3B%20%22%2CAW4%3AAW%22%20%26amp%3B%20m%20%26amp%3B%20%22%2CAZ4%3AAZ%22%20%26amp%3B%20m%20%26amp%3B%20_%0A%20%20%20%20%20%20%20%20%20%20%20%20%22%2CBB4%3ABB%22%20%26amp%3B%20m%20%26amp%3B%20%22%2CBD4%3ABD%22%20%26amp%3B%20m%20%26amp%3B%20%22%2CBM4%3ABM%22%20%26amp%3B%20m%20%26amp%3B%20%22%2CBO4%3ABO%22%20%26amp%3B%20m)%0A%20%20%20%20%20%20%20%20.Replace%20What%3A%3D%22.%22%2C%20Replacement%3A%3D%22%2F%22%0A%20%20%20%20%20%20%20%20.Replace%20What%3A%3D%22-%22%2C%20Replacement%3A%3D%22%2F%22%0A%20%20%20%20%20%20%20%20.Replace%20What%3A%3D%22_%22%2C%20Replacement%3A%3D%22%2F%22%0A%20%20%20%20End%20With%0A%0A%20%20%20%20'Application.Calculation%20%3D%20xlCalculationautomatic%0A%20%20%20%20Application.DisplayAlerts%20%3D%20True%0A%20%20%20%20Application.EnableEvents%20%3D%20True%0A%20%20%20%20Application.ScreenUpdating%20%3D%20True%0AEnd%20Sub%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EBut%20are%20all%20dates%20in%20ddmmyyyy%20order%3F%20Or%20all%20in%20mmddyyyy%20order%3F%20If%20there%20is%20a%20mixture%2C%20you%20have%20a%20problem...%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2833689%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20standardize%20dates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2833689%22%20slang%3D%22en-US%22%3EIt%20was%20almost%20instantaneous%20%3A-)%2C%20I'll%20try%20to%20understand%20the%20changes%20and%20find%20out%20why%20it%20was%20super%20fast%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%3CBR%20%2F%3EDates%20are%20all%20in%20dd%2Fmm%2Fyyyy%20format.%3CBR%20%2F%3EThank%20you%20for%20your%20help%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2833980%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20standardize%20dates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2833980%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1084899%22%20target%3D%22_blank%22%3E%40csoares%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThe%20code%20is%20so%20fast%20because%20it%20does%20not%20loop%20through%20the%20cells.%20It%20uses%20the%20Replace%20method%20of%20the%20range%20to%20act%20on%20all%20cells%20in%20one%20go.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2834287%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20standardize%20dates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2834287%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3E%40Hans%20Vogelaar%3C%2FA%3E%26nbsp%3BA%20strange%20thing%20is%20happening%2C%20if%20you%20see%20in%20the%20attached%20image%2C%20the%20cell%20is%20formatted%20as%20Date%20%22%2F%22%2C%20but%20in%20the%20visible%20part%20of%20the%20cell%20is%20the%20date%20with%20%22-%22%2C%20but%20if%20we%20place%20the%20mouse%20on%20the%20cell%2C%20we%20can%20see%20that%20it%20is%20recorded%20as%20%22%2F%22.%3CBR%20%2F%3EWhen%20running%20the%20code%20below%2C%20visually%2C%20the%20cell%20always%20has%20the%20%22-%22.%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-visual-basic%22%3E%3CCODE%3EDim%20m%20As%20Long%0Am%20%3D%20Folha3.Range(%22C%22%20%26amp%3B%20Folha3.Rows.Count).End(xlUp).Row%0AWith%20Range(%22AT4%3AAT%22%20%26amp%3B%20m)%0A%20%20%20%20.Replace%20What%3A%3D%22.%22%2C%20Replacement%3A%3D%22%2F%22%0A%20%20%20%20.Replace%20What%3A%3D%22-%22%2C%20Replacement%3A%3D%22%2F%22%0A%20%20%20%20.Replace%20What%3A%3D%22_%22%2C%20Replacement%3A%3D%22%2F%22%0AEnd%20With%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%3CBR%20%2F%3EWhy%20is%20this%20happening%3F%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Captura%20de%20ecr%C3%A3%202021-10-11%20143401.jpg%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F316503i11B2F3D93F3D202E%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Captura%20de%20ecr%C3%A3%202021-10-11%20143401.jpg%22%20alt%3D%22Captura%20de%20ecr%C3%A3%202021-10-11%20143401.jpg%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
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 (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.