Changing the date format to yyyy-mm-dd

by

UPDATED: NEW ANSWER

Here is a solution that will do the job! The sub routine includes a function that does the replacement (the function itself is really useful!). Run the sub and all occurances in column A will be fixed.

Sub FixDates()

Dim cell As range
Dim lastRow As Long

lastRow = range("A" & Rows.count).End(xlUp).Row

For Each cell In range("A1:A" & lastRow)
    If InStr(cell.Value, ".") <> 0 Then
        cell.Value = RegexReplace(cell.Value, _
        "(\d{2})\.(\d{2})\.(\d{4})", "$3-$2-$1")
    End If
    If InStr(cell.Value, "https://stackoverflow.com/") <> 0 Then
        cell.Value = RegexReplace(cell.Value, _
        "(\d{2})/(\d{2})/(\d{4})", "$3-$1-$2")
    End If
    cell.NumberFormat = "yyyy-mm-d;@"
Next

End Sub

Place this function in the same module:

Function RegexReplace(ByVal text As String, _
                      ByVal replace_what As String, _
                      ByVal replace_with As String) As String

Dim RE As Object
Set RE = CreateObject("vbscript.regexp")

RE.pattern = replace_what
RE.Global = True
RegexReplace = RE.Replace(text, replace_with)

End Function

How it works: I have a nifty RegexReplace function that allows you to do replace using regular expressions. The sub mearly loops through your A column and does a regex replace for those 2 cases you mentioned. The reason I use an Instr() first is to determain if it needs the replacement, and which kind. You could technically skip this but doing replace on cells that don’t need it is really costly. At the end I format the cell to your custom date format regardless of what’s inside for safe measure.

In case you aren’t familiar with Regex (for ref: http://www.regular-expressions.info/), the expression I am using is:

  • Each item in () are capture groups – aka, the stuff you want to mess with
  • \d stands for a number [0-9].
  • {2} means 2 of, and {4} mean 4 of. I have been explicit here for safety.
  • The \ before the . in the first replace is needed since “.” has special meaning.
  • In VBA regex, you refer to capture groups by using $ + no. of group. This is how I flip the order of the 3 items.

More Related Contents:

  1. Autofilter with column formatted as date
  2. Excel VBA: Convert a date string to a Unix timestamp
  3. Convert month name into number
  4. Test or check if sheet exists
  5. How to copy Outlook mail message into excel using VBA or Macros
  6. For Loop not fully cycling in excel VBA [duplicate]
  7. Unique Random Numbers using VBA
  8. Populate unique values into a VBA array from Excel
  9. How to add a named sheet at the end of all Excel sheets?
  10. VBA general way for pulling data out of SAP [closed]
  11. Closing Excel Application using VBA
  12. Delete Row based on Search Key VBA
  13. How to add a button programmatically in VBA next to some sheet cell data?
  14. What are the benefits of setting objects to “Nothing”
  15. Faster way to remove ‘extra’ spaces (more than 1) from a large range of cells using VBA for Excel
  16. Uncheck all checkboxes across entire workbook via CommandButton
  17. return csv file as recordset
  18. VBA workbooks.Close without being prompted to if the user wants to save?
  19. Extract Data from Word Document to an Excel SpreadSheet
  20. VBA equivalent to Excel’s mod function
  21. Copy a selected range to another worksheet
  22. Excel macro – paste only non empty cells from one sheet to another
  23. The MacScript function is not working well in Office for Mac 2016! Any ideas?
  24. Pointers needed for speeding up nested loop macro in VBA
  25. ‘1004’: “The sort reference is not valid.”
  26. Select entire column in table using Excel VBA
  27. Create hyperlink to another sheet
  28. Reducing WithEvent declarations and subs with VBA and ActiveX
  29. Convert Early Binding VBA to Late Binding VBA : Excel to Outlook Contacts
  30. How to get the range of the visible rows after applying an advanced filter in Excel (VBA)

Leave a Comment