Change date format to yyyy-mm-dd

Question

I have a date column that contains dates in a mixed format. For instance:

a
03/21/1990
03/21/1990

So basically there are two different formats in one column: dd.mm.yyyy and mm/dd/yyyy . I am trying to write a VBA script to change the format of all dates in the yyyy-mm-dd column. This is what I have so far:

 Sub changeFormat() Dim rLastCell As Range Dim cell As Range, i As Long Dim LValue As String i = 1 With ActiveWorkbook.Worksheets("Sheet1") Set rLastCell = .Range("A65536").End(xlUp) On Error Resume Next For Each cell In .Range("A1:A" & rLastCell.Row) LValue = Format(cell.Value, "yyyy-mm-dd") .Range("B" & i).Value = LValue i = i + 1 Next cell On Error GoTo 0 End With End Sub 

I know this is not an elegant piece of code, but I'm starting with VBA, so please forgive me. The problem with this code is that it just rewrites the unaltered column to column B, when I change the argument in the Format function from yyyy-mm-dd to dd/mm/yyyy , it works, but only for dates in mm/dd/yyyy format mm/dd/yyyy and leaves dd.mm.yyyy untouched. I would appreciate any advice.

Answer 1

UPDATED: NEW ANSWER

Here is the solution that will make this work! The subroutine includes a function that performs a replacement (the function itself is really useful!). Run sub and all entries 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, "/") <> 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 

Put 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 great RegexReplace function that allows me to do replacements using regular expressions. Submearly loops through your column A and replaces the regex for the two cases you mentioned. The reason I use Instr () first is to determine if it needs a replacement and what kind. You can technically skip this, but doing a replacement on cells that are not needed is really expensive. In the end, I will format the cell in a custom date format no matter what is inside for a safe measure.

If you are not familiar with Regex (for reference: http://www.regular-expressions.info/ ), I use the following expression:

• Each element in () - capture groups - otherwise, the material you want to ruin
• \ d denotes the number [0-9].
• {2} means 2 of, and {4} - 4 of. I was here clearly for safety.
• \ before. in the first substitution is necessary, since "." is of particular importance.
• In a VBA regular expression, you reference capture groups using $ + no. groups. This is how I reverse the order of 3 elements.

Answer 2

See if this does what you want. You may need to adapt it a bit for your application.

Hope this helps!

 Sub convertDates() Dim rRng As Range Dim rCell As Range Dim sDest As String Dim sYear, sMonth, sDay, aDate 'Range where the dates are stored, excluding header Set rRng = Sheet1.Range("A2:A11") 'Column name of destination sDest = "B" 'You could also use the following, and just select the range. 'Set rRng = Application.selection For Each rCell In rRng.Cells sYear = 99999 If InStr(rCell.Value, ".") > 0 Then aDate = Split(rCell.Value, ".") If UBound(aDate) = 2 Then sDay = aDate(0) sMonth = aDate(1) sYear = aDate(2) End If ElseIf InStr(rCell.Value, "/") > 0 Then aDate = Split(rCell.Value, "/") If UBound(aDate) = 2 Then sDay = aDate(1) sMonth = aDate(0) sYear = aDate(2) End If End If With rCell.Range(sDest & "1") If sYear <> 99999 Then On Error Resume Next .Value = "'" & Format(CDate(sMonth & "/" & sDay & "/" & sYear), "YYYY-MM-DD") 'If it can't convert the date, just put the original value in the dest 'cell. You can tailor this to your own preference. If Err.Number <> 0 Then .Value = rCell.Value On Error GoTo 0 Else .Value = rCell.Value End If End With Next End Sub 

Answer 3

You do not need VBA for this. This one-sheet worksheet formula will do the trick:

 =IF(ISERROR(FIND(".",A1)),IF(ISERROR(FIND("/",A1)),"invalid format", DATE(RIGHT(A1,4),LEFT(A1,2),MID(A1,4,2))), DATE(RIGHT(A1,4),MID(A1,4,2),LEFT(A1,2))) 

This assumes that the day and month are always indicated as two-digit numbers (for example, always 03, not just 3), and the year has four digits (that is, "limited" to 1000-9999). But if this does not apply to you, then the formula can be easily adjusted to suit your needs.

Answer 4

@ Jean-François Corbett has a formula solution that works, but can be reduced by more than half due to rejection of the error message (based on the fact that #VALUE! Is informative) and another IF, both DATE, using IFERROR than ISERROR and SUBSTITUTE instead of one LEFT, MID, RIGHT set:

 =IFERROR(1*(MID(A1,4,3)&LEFT(A1,3)&RIGHT(A1,4)),1*SUBSTITUTE(A1,".","/")) 

This applies to the interpretation mentioned by @Issun in the OP commentary, and assumes that the output will be associated with cells formatted by yyyy-mm-dd .

It can be written using such a routine:

 Sub Macro1() Range("B1:B10").Formula = "=IFERROR(1*(MID(A1,4,3)&LEFT(A1,3)&RIGHT(A1,4)),1*SUBSTITUTE(A1,""."",""/""))" End Sub 

Answer 5

Month (Date) and "-" and "Day" (Date) and "-" and Year (Date)

Answer 6

Here is a simple solution:

  Sub ChangeFormat1() Dim ws as Worksheet Set ws = Thisworkbook.Sheets("Sheet1") LR = ws.cells(rows.count,1).End(Xlup).Row with ws For I = 1 to LR .cells(I,2).value = .cells(I,1).value Next End with ws.range("B1:B" & LR).numberformat = "yyyy-mm-dd" End Sub