VBA: Correct Date Formatting in Excel

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'''         Designed by Luc Schulz
'''         March 27, 2018
'''
'''         The purpose of this module is to fix incorrectly
'''         formatted dates in Excel Spreadsheets.
'''
'''         To use this tool, select the dates with the wrong
'''         formatting, then run the CorrectSelectedDates() Sub.
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

Option Explicit

Sub CorrectSelectedDates()

    Dim rng As Range
    Set rng = Selection
    
    Dim c As Variant
    For Each c In rng
        
        Dim dt As String
        dt = Trim(c.Value)
        
        If IsDateFormattedBadly(dt) = True Then
            c.NumberFormat = "yyyy-mm-dd;@"
            c.Value = FixIncorrectDateFormat(dt)
        End If
    Next

End Sub

' RETURNS TRUE IF THE DATE IS FORMATTED AS MM/DD/YYYY OR AS DD/MM/YYYY
Private Function IsDateFormattedBadly(cellValue As String) As Boolean
        
    If InStr(cellValue, "/") = 3 And InStr(5, cellValue, "/") = 6 Then
        IsDateFormattedBadly = True
    End If
    
End Function

'SEPARATES THE DATE STRING INTO YEAR, MONTH AND DAY THEN CONVERTS IT INTO A DATESERIAL VALUE
Private Function FixIncorrectDateFormat(cellValue As String) As Date
    
    Dim y As String, m As String, d As String
    
    y = Right(cellValue, 4)
    m = Left(cellValue, 2)
    d = Mid(cellValue, 4, 2)
    
    If CInt(m) <= 12 Then
        FixIncorrectDateFormat = DateSerial(y, m, d)
    Else
        FixIncorrectDateFormat = DateSerial(y, d, m)
    End If
    
End Function

Leave a Reply

Your email address will not be published. Required fields are marked *