Formatting Correct Date Thread VBA

[HTMLGhozt]

Hello Again!

I'm using Excel 2010

I have a range of dates which are 2012012 (DMMYYYY) and I need them to be in the DD/MM/YYYY format. I have little VBA experience but simply formatting the cells to date provides me with 9/12/7408 from 2012012 or simply hashtags.

I don't have any snippets of code which relate to formatting, but here's what I'm using to transfer the information to sheet.

Sub Carrier_EFF_DT(ws, ws2, aCell, rng, col, LastRow, colName)


'This statement finds the EFF_DT column in the carrier report.
    With ws
        Set aCell = .Range("A1:ZZ1").Find(What:="EFF_DT", LookIn:=xlValues, LookAt:=xlWhole, _
                    MatchCase:=False, SearchFormat:=False)


        'If the EFF_DT column is found, then:
        If Not aCell Is Nothing Then
            col = aCell.Column
            colName = Split(.Cells(, col).Address, "$")(1)
            
            LastRow = .Range(colName & .Rows.Count).End(xlUp).Row
            
            'This is the range of rows in the column specified (EFF_DT).
            Set rng = .Range(colName & "2:" & colName & LastRow)
            
            Debug.Print rng.Address
            
            'This copies your range, and pastes it at H4, or the Effective Date column in the Master Spreadsheet.
            rng.Copy Destination:=ws2.Range("H4")


        'If the macro was unable to find the EFF_DT column, then it will alert you.
        Else
            MsgBox "Effective date not found!"
        End If
        
    End With
    
End Sub

 

[Rick Rothstein]

Try this manual procedure... select the column with the values you want to convert to dates, call up the "Text To Columns" dialog box (Data tab, Data Tools panel, Text To Columns button)... click the Next button twice and then select the Date option button (upper left of dialog box) and then select DMY from the drop down next to it... click the Finish button. Your values should now be real dates.

 

[HTMLGhozt]

Tried the manual procedure! Out of the range cells which had two digit months formatted correctly, the rest did not format at all.

Also I made a mistake the date format is MDDYYYY, and I need it to be MM/DD/YYYY.

 

[Rick Rothstein]

Out of the range cells which had two digit months formatted correctly, the rest did not format at all.

You said "I have a range of dates which are 2012012 (DMMYYYY)"... the MM means that all your dates would be two digits with a leading zero for those months that are single digits. Are you saying that I not true? If so, does that mean today's date could be entered like this...

462015

I hope not, because if so, how would you ever know whether 1232015 was January 23rd or December 3rd?

 

[HTMLGhozt]

Unfortunately, That is what I am saying. Today's date would be 4062015, January 23rd of this year would be 1232015 and December 3rd 12032015. The format is actually MDDYYYY.

 

[HTMLGhozt]

Bump!

 

[FormR]

You could try adding the below directly after line "rng.Copy Destination:=ws2.Range("H4")" in your existing macro

With ws2.Range("H4").Resize(rng.Rows.Count)
    .Cells = Evaluate("=INDEX(TEXT(" & .Address & ",""00\/00\/0000"")+0,0)")
End With

 

[HTMLGhozt]

It changed the format from 2012012 to 40940. Which I don't even understand.

You could try adding the below directly after line "rng.Copy Destination:=ws2.Range("H4")" in your existing macro

With ws2.Range("H4").Resize(rng.Rows.Count)
    .Cells = Evaluate("=INDEX(TEXT(" & .Address & ",""00\/00\/0000"")+0,0)")
End With

 

[HTMLGhozt]

Did a little research found out that was Excel's way of storing dates I am now writing a code to reformat the column.

 

[FormR]

Did a little research found out that was Excel's way of storing dates I am now writing a code to reformat the column.

Correct, for formatting try:

With ws2.Range("H4").Resize(Rng.Rows.Count)
    .Cells = Evaluate("=INDEX(TEXT(" & .Address & ",""00\/00\/0000"")+0,0)")
    .NumberFormat = "MM/DD/YYYY"
End With