[vba] Excel VBA date formats

I've got a spreadsheet that contains a number of dates. These generally appear in either mm/dd/yyyy or mm/dd/yyyy hh:mm.

The problem is that the dates aren't always put in correctly and I want to have checks to make sure they are dates in the code.

My original thought was to use IsDate to check or CDate but this didn't seem to work: it was still returning strings instead of dates.

I've since set up a small experiment which shows that these functions don't work the way I expect them to. Methodology is:

1. In a cell A1 I enter the formula =DATE(2013,10,28)
2. Cell B1 formula =A1*1 which should equal a number (41575)

3. Run this little script

   Sub test()
   
   MsgBox ("Start:" & TypeName(ActiveCell.Value) & " " & IsDate(ActiveCell.Value))
   
   ActiveCell.Value = Format(ActiveCell.Value, "mm/dd/yyyy")
   MsgBox ("After format: " & TypeName(ActiveCell.Value) & " " & IsDate(ActiveCell.Value))
   
   ActiveCell.Value = CDate(ActiveCell.Value)
   MsgBox ("After Cdate: " & TypeName(ActiveCell.Value) & " " & IsDate(ActiveCell.Value))
   
   End Sub
   

When the script starts the cell is a of type date and IsDate returns true. After it is run through Format it is of type string but IsDate still returns true. CDate will also convert the cell to a string. Cell B1 will also now return 0 (since its a string*1).

So I guess to summarize the questions:

1. Why are Format and CDate changing my cells to strings?
2. How can I ensure that a cell will return a date value and not just a string that looks like a date?

Thanks for the input. I'm obviously seeing some issues that aren't being replicated on others machines. Based on Jean's answer I have come up with less elegant solution that seems to work.

Since if I pass the cell a value directly from cdate, or just format it as a number it leaves the cell value as a string I've had to pass the date value into a numerical variable before passing that number back to the cell.

Function CellContentCanBeInterpretedAsADate(cell As Range) As Boolean
    Dim d As Date
    On Error Resume Next
    d = CDate(cell.Value)
    If Err.Number <> 0 Then
        CellContentCanBeInterpretedAsADate = False
    Else
        CellContentCanBeInterpretedAsADate = True
    End If
    On Error GoTo 0
End Function

Example usage:

Dim cell As Range
dim cvalue as double
Set cell = Range("A1")

If CellContentCanBeInterpretedAsADate(cell) Then
    cvalue = cdate(cell.value)
    cell.value = cvalue
    cell.NumberFormat = "mm/dd/yyyy hh:mm"
Else
    cell.NumberFormat = "General"
End If

Format converts the values to strings. IsDate still returns true because it can parse that string and get a valid date.

If you don't want to change the cells to string, don't use Format. (IOW, don't convert them to strings in the first place.) Use the Cell.NumberFormat, and set it to the date format you want displayed.

ActiveCell.NumberFormat = "mm/dd/yy"   ' Outputs 10/28/13
ActiveCell.NumberFormat = "dd/mm/yyyy" ' Outputs 28/10/2013

Use value(cellref) on the side to evaluate the cells. Strings will produce the "#Value" error, but dates resolve to a number (e.g. 43173).

To ensure that a cell will return a date value and not just a string that looks like a date, first you must set the NumberFormat property to a Date format, then put a real date into the cell's content.

Sub test_date_or_String()
 Set c = ActiveCell
 c.NumberFormat = "@"
 c.Value = CDate("03/04/2014")
   Debug.Print c.Value & " is a " & TypeName(c.Value) 'C is a String
 c.NumberFormat = "m/d/yyyy"
   Debug.Print c.Value & " is a " & TypeName(c.Value) 'C is still a String
 c.Value = CDate("03/04/2014")
   Debug.Print c.Value & " is a " & TypeName(c.Value) 'C is a date    
End Sub