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...
excel
Macros and VBA
  • HansVogelaar's avatar
    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...

csoares's avatar
csoares
Brass Contributor
Oct 11, 2021
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
HansVogelaar's avatar
HansVogelaar
MVP
Oct 11, 2021

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.

  • csoares's avatar
    csoares
    Brass Contributor
    Oct 12, 2021
    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
    • HansVogelaar's avatar
      HansVogelaar
      MVP
      Oct 12, 2021

      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.

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

    HansVogelaar 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?

    • HansVogelaar's avatar
      HansVogelaar
      MVP
      Oct 11, 2021

      csoares Select from A4 down to the last used row,

      Set the number format to dd/mm/yyyy (or its equivalent in Portuguese)

Resources