Type-mismatch/run-time error in case of CDate/IsDate

Question

I would like to count the number of the cells which contain dates which are older then 1 year.

But I see cells with incorrect date format or text, so in case of IsDate=false I would like to delete the contents of the cells. After I check with the next If cycle is it older than 1 year or not.

But I get type-mismatch error in case of cell which cointains "201.07.01" (instead of 2021.07.01). But the IsDate is True in this case too.

If IsDate(Cells(1, 1)) = True Then
Else
Cells(1, 1).ClearContents
End If

If Cells(1, 1) + 365 < Date Then
MsgBox ("older")
Else
MsgBox ("not older")
End If

I tried to replace "." to "-" and convert the content of the cell to date with CDate with the following code but in this cade I get Run-time error '1004': Application-defined or object-defined error.

Cells(1, 1) = Replace(Cells(1, 1), ".", "-")

If IsDate(Cells(1, 1)) = True Then
Cells(1, 1) = CDate(Cells(1, 1))
Else
Cells(1, 1).ClearContents
End If

If Cells(1, 1) + 365 < Date Then
MsgBox ("older")
Else
MsgBox ("not older")
End If

What can be the issue and how could I solve it? Thanks in advance!

Answer 1

Assume cell A1 has the string "201.07.01". Excel recognises this as a string.

Short Story:

• You got your first error because Cells(1, 1) + 365 attempts to add a string to a number.
• You got your second error because Cells(1, 1) = CDate(Cells(1, 1)) tries to enter a date that excel doesn't recognise.

Long Story:

The issue is the way dates are represented in Excel vs VBA. Try:

Sub excel_vs_vba_dates()
  Debug.Print "Day 1 in Excel: ", WorksheetFunction.Text(1, "yyyy-mm-dd")
  Debug.Print "Day 1 in VBA: ", Format(1, "yyyy-mm-dd")

  'Excel doesn't accept negative numbers as dates
  On Error Resume Next
  Debug.Print "Day -100 in Excel: ", WorksheetFunction.Text(-100, "yyyy-mm-dd")
  Debug.Print Err.Description
  On Error GoTo 0
  
  'Negative numbers are valid dates in vba
  Debug.Print "Day -100 in VBA: ", Format(-100, "yyyy-mm-dd")
  
  Debug.Print "201-07-01 in VBA: ", Format(CDate("201-07-01"), "yyyy-mm-dd")
  
  Dim lng As Long: lng = DateSerial(Year(CDate("201-07-01")), _
                                    Month(CDate("201-07-01")), _
                                    Day(CDate("201-07-01")))
  Debug.Print "201-07-01 as a number in VBA: ", lng
End Sub

Output:

Day 1 in Excel:             1900-01-01
Day 1 in VBA:               1899-12-31
Unable to get the Text property of the WorksheetFunction class
Day -100 in VBA:            1899-09-21
201-07-01 in VBA:           0201-07-01
201-07-01 as a number in VBA:             -620364 

This is why Cells(1, 1) = CDate(Cells(1, 1)) will raise an error if the value in that cell is "201.07.01" as you are trying to copy into Excel a date that it doesn't recognise. Please note using "." or "-" depends of your system's locale settings.

On the other hand, if you use CDate(Cells(1, 1)) in your code, then VBA is happy to use it as the date "1st of July 201".

The following function works for me:

Function checkDate(rng As Range) As String
  Set rng = rng.Cells(1, 1)
  
  If IsDate(rng.Value) Then
    If CDate(rng.Value) < DateAdd("yyyy", -1, Date) Then
      checkDate = "older"
    Else
      checkDate = "not older"
    End If
  Else
    rng.ClearContents
    checkDate = "invalid date"
  End If
End Function

Sub testDate()
  MsgBox checkDate(Cells(1, 1))
End Sub