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

excel

Macros and VBA

HansVogelaar

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

PeterBartholomew1

Silver Contributor

Oct 11, 2021

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

Brass Contributor

Oct 12, 2021

Thanks for the explanation, I will need some study to understand the code, it's always good to learn.

Forum Discussion

How to standardize dates

Resources