CDate Help

reberryjr

Well-known Member
Joined
Mar 16, 2017
Messages
519
Office Version
  1. 2013
Platform
  1. Windows
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.
VBA Code:
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.
VBA Code:
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.
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
59,532
Office Version
  1. 365
Platform
  1. Windows
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.
VBA Code:
    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"
 
Solution

jsb1921

Board Regular
Joined
Aug 14, 2020
Messages
70
Office Version
  1. 2007
Platform
  1. Windows
  2. Web
VBA Code:
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
59,532
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Mar 16, 2017
Messages
519
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

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?
 

reberryjr

Well-known Member
Joined
Mar 16, 2017
Messages
519
Office Version
  1. 2013
Platform
  1. Windows
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
59,532
Office Version
  1. 365
Platform
  1. Windows
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.
 

Forum statistics

Threads
1,144,611
Messages
5,725,302
Members
422,608
Latest member
bswg5882

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top