Link to home
Start Free TrialLog in
Avatar of mcrmg
mcrmg

asked on

formatting data

Hi,

Please see the file attached. There are two columns, each column represents date, but the format is inconsistent.  Is there a way to use a simple VBA to look for specific columns and change the value to mm/dd/yyyy.

thanks
Avatar of Subodh Tiwari (Neeraj)
Subodh Tiwari (Neeraj)
Flag of India image

You forgot to attach the file.
Avatar of mcrmg
mcrmg

ASKER

huh...sorry
test--1-.xlsx
ASKER CERTIFIED SOLUTION
Avatar of Subodh Tiwari (Neeraj)
Subodh Tiwari (Neeraj)
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Add this macro and execute it.

Sub FormatDate()
    Dim cel As Range
    
    For Each cel In Range("A2:B" & ActiveSheet.UsedRange.Rows.Count)
        If Len(cel) > 5 Then
            If Not IsDate(cel) Then
                cel = Left$(cel, 4) & "/" & Mid$(cel, 2, 2) & "/" & Right$(cel, 2)
            End If
        End If
        cel.NumberFormat = "mm/dd/yyyy"
    Next
    
End Sub

Open in new window

Column A all seem OK but column B is the issue. In column C try this formula, starting in C2:

=IF(B2<19000000,B2,DATE(LEFT(B2,4),MID(B2,5,2),RIGHT(B2,2)))

Copy down as far as required, the values from column C can then be copied and pasted as values into column B.

I have assumed the erroneous dates at the bottom of the column are in yyyymmdd format. If that's not the case then the month and day parts of the DATE function will need swapping.
Apologies I did not look down column A far enough.

Try this instead.
Column C for column A dates:
=IF(A2="","",IF(A2<19000000,A2,DATE(LEFT(A2,4),MID(A2,5,2),RIGHT(A2,2))))

Column D for column D dates:
=IF(B2="","",IF(B2<19000000,B2,DATE(LEFT(B2,4),MID(B2,5,2),RIGHT(B2,2))))

Copy and paste values as before.
Avatar of mcrmg

ASKER

thank you very much
You're welcome!