CDate Help

[reberryjr]

I've never used the CDate function, and after some research, it looks like that's what I should use, but I'm not understanding "how", as my data set seems to differ from the examples I've seen online. In column C of my worksheet, I have some entries of dates that are stored as text. Example: 030621. I'm trying to convert that to 03/06/21.

If I run the first example, I get a returned value of 06/19/82.

With mws.Range("C2:C" & mLR)
    '.Value = Format(CDate(Cells(.Value, "######")), "MM/DD/YY")
    .NumberFormat = "MM/DD/YY"
    .Value = .Value
End With

If I run the second example, I get a type mismatch error.

With mws.Range("C2:C" & mLR)
    .Value = Format(CDate(Cells(.Value, "######")), "MM/DD/YY")
    '.NumberFormat = "MM/DD/YY"
    '.Value = .Value
End With

I'm sure I'm doing something wrong, but I haven't been able to identify what it is.

 

[Joe4]

The CDATE function will convert the entry to a valid date.
But the FORMAT function turns it back to text.
There are some other issues with how you are trying to do it (I am not sure that apply the formula to a whole range of cells like that).

Quite frankly, I think I would just use Text to Columns on this, i.e.

    mws.Activate
    Columns("G:G").TextToColumns Destination:=Range("G1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 3), TrailingMinusNumbers:=True
    Columns("G:G").NumberFormat = "mm/dd/yy"

 

[jsb1921]

Sub Macro1()
Dim str1 As String
Dim strmm As String, strdd As String, stryyyy As String
Dim mydate As Date

' take the value in c2
str1 = Trim(LTrim(Cells(2, "C").Value))
If Len(str1) = 5 Then
    str1 = "0" & str1
End If
strmm = Mid(str1, 1, 2)
strdd = Mid(str1, 3, 2)
stryyyy = "20" & Mid(str1, 5, 2)

'put the result in d2
'if you want to format the whole column
'Columns("d:d").Select
'Selection.NumberFormat = "mm/dd/yy;@"
'if you want to format one cell
Cells(2, "d").Select
Selection.NumberFormat = "mm/dd/yy;@"
mydate = DateSerial(stryyyy, strmm, strdd)
Cells(2, "d").Value = mydate

' will print 3/6/2021
Debug.Print Application.WorksheetFunction.Text(mydate, "m/d/yyyy")
End Sub

 

[Joe4]

Sub Macro1() Dim str1 As String Dim strmm As String, strdd As String, stryyyy As String Dim mydate As Date ' take the value in c2 str1 = Trim(LTrim(Cells(2, "C").Value)) If Len(str1) = 5 Then str1 = "0" & str1 End If strmm = Mid(str1, 1, 2) strdd = Mid(str1, 3, 2) stryyyy = "20" & Mid(str1, 5, 2) 'put the result in d2 'if you want to format the whole column 'Columns("d:d").Select 'Selection.NumberFormat = "mm/dd/yy;@" 'if you want to format one cell Cells(2, "d").Select Selection.NumberFormat = "mm/dd/yy;@" mydate = DateSerial(stryyyy, strmm, strdd) Cells(2, "d").Value = mydate ' will print 3/6/2021 Debug.Print Application.WorksheetFunction.Text(mydate, "m/d/yyyy") End Sub

That is a bit of overkill, and only updates one single cell, not a whole column of cells.
It can be done a lot simpler.

 

[reberryjr]

That is a bit of overkill, and only updates one single cell, not a whole column of cells.
It can be done a lot simpler.

What would your recommendation be?

 

[Joe4]

What would your recommendation be?

My first reply.
Did you try the code I provided in that post?

 

[reberryjr]

My first reply.
Did you try the code I provided in that post?

My apologies...when I accessed the responses yesterday, I didn't see your response. I just tried it, and it works wonderfully. Thank you!

 

[Joe4]

My apologies...when I accessed the responses yesterday, I didn't see your response. I just tried it, and it works wonderfully. Thank you!

You are welcome. Glad I was able to help!

Yes, I have noticed that when there are multiple replies to someone's post, many times they just look at the last one, and don't notice that there are actually more than one reply.