EXCEL VBA Date format question

I am working in Excel VBA and am trying to convert a string to date format and then calculate the number of days between the newdate (strdate formatted to Date type) and Todays date which is stored in my Excel spreadsheet.  I am getting a type mismatch error on the following line of code:

 newdate = "=DATE(strdate)"

The entire subroutine is listed below:

Sub fmtdate(ByVal agedate)
Dim newdate As Date
Dim strdate As String
Dim length As Integer
TodaysDate = ActiveSheet.Range("E2")
length = Len(Trim(agedate))

Select Case length
    Case Is = 5
        strdate = Right(agedate, 2) & "/0" & (Left(agedate, 1)) & "/" _
         & Mid(agedate, 2, 2)
          newdate = "=DATE(strdate)"
       ActiveCell.Offset(0, 4).FormulaR1C1 = "=DAYS360(newdate,TodaysDate)"
    Case Is = 6
        strdate = Right(agedate, 2) & "/" & (Left(agedate, 2)) & "/" _
       & Mid(agedate, 3, 2)
         newdate = "=DATE(strdate)"
        ActiveCell.Offset(0, 4).FormulaR1C1 = "=DAYS360(newdate,TodaysDate)"
End Select      
End Sub

Help would be greatly appreciated!!
Who is Participating?
lynx20Connect With a Mentor Commented:

Try newdate = DateValue(strdate)

jeveristConnect With a Mentor Commented:
Hi Jeanniem,

Try changing this:

newdate = "=DATE(strdate)"

to this:

newdate = CVDate(strdate)

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.