formatting data

mcrmg
mcrmg used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Subodh Tiwari (Neeraj)Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015

Commented:
You forgot to attach the file.

Author

Commented:
huh...sorry
test--1-.xlsx
Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015
Commented:
You may try something like this...
Sub FormatDates()
Dim cell As Range
Dim lr As Long
Dim dtStr As String
Application.ScreenUpdating = False
lr = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
For Each cell In Range("A2:B" & lr)
    If cell <> "" And IsNumeric(cell.Value) And Evaluate(Len(cell.Text)) > 5 Then
        dtStr = cell.Value
        cell.Value = DateSerial(Left(dtStr, 4), Mid(dtStr, 5, 2), Right(dtStr, 2))
    End If
Next cell
Range("A2:B" & lr).NumberFormat = "mm/dd/yyyy"
Application.ScreenUpdating = True
End Sub

Open in new window

Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Martin LissOlder than dirt
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
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

Rob HensonFinance Analyst

Commented:
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.
Rob HensonFinance Analyst

Commented:
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.

Author

Commented:
thank you very much
Subodh Tiwari (Neeraj)Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015

Commented:
You're welcome!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial