Forum Discussion
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
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
- PeterBartholomew1Silver Contributor
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.
- csoaresBrass ContributorThanks for the explanation, I will need some study to understand the code, it's always good to learn.
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...
- csoaresBrass ContributorIt 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 helpThe 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.