I am struggling with formatting dates in Excel and am looking for some help.

I have two different date formats in the same column: MM/DD/YYYY DD/MM/YY

I am struggling to change the DD/MM/YY to MM/DD/YYYY. I have tried so many things and can't get it to work.

Public Function dateguesser(inDate As String) As Date
    dateguesser = CDate(Split(Split(inDate, " ")(0), ",")(0)) 
End Function

Things I have tried:

1. This solution ends up thinking that the DD in the DD/MM/YY format is the year because the year is not stored as four digits.

2. Tried to change MM/DD/YYYY to DD/MM/YY and then convert all of them to MM/DD/YYYY.

3. Text to columns

CodePudding user response：

If you put this formula starting in B3, I think it should work for all values?

=IF(ISNUMBER(A3),A3,DATE(2000 RIGHT(A3,2),MID(A3,4,2) 0,LEFT(A3,2) 0))

You could also probably modify your custom function to something like this:

Public Function dateguesser(inDate As Variant) As Date
   If IsNumeric(inDate) Then
      dateguesser = inDate
   Else

    dateguesser = CDate(Split(Split(inDate, " ")(0), ",")(0))
   End If
   
End Function

CodePudding user response：

I'd add another column to convert to epoch and back to new date format. https://exceljet.net/formula/convert-unix-time-stamp-to-excel-date could help