[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 228
  • Last Modified:

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!!
0
Jeanniem
Asked:
Jeanniem
2 Solutions
 
lynx20Commented:
Hi

Try newdate = DateValue(strdate)

Bob
0
 
jeveristCommented:
Hi Jeanniem,

Try changing this:

newdate = "=DATE(strdate)"


to this:

newdate = CVDate(strdate)

Jim
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now