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
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
You forgot to attach the file.
ASKER
huh...sorry
test--1-.xlsx
test--1-.xlsx
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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(LE FT(B2,4),M ID(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.
=IF(B2<19000000,B2,DATE(LE
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<1900000 0,A2,DATE( LEFT(A2,4) ,MID(A2,5, 2),RIGHT(A 2,2))))
Column D for column D dates:
=IF(B2="","",IF(B2<1900000 0,B2,DATE( LEFT(B2,4) ,MID(B2,5, 2),RIGHT(B 2,2))))
Copy and paste values as before.
Try this instead.
Column C for column A dates:
=IF(A2="","",IF(A2<1900000
Column D for column D dates:
=IF(B2="","",IF(B2<1900000
Copy and paste values as before.
ASKER
thank you very much
You're welcome!