Excel VBA date formats

It's important to distinguish between the content of cells, their display format, the data type read from cells by VBA, and the data type written to cells from VBA and how Excel automatically interprets this. (See e.g. this previous answer.) The relationship between these can be a bit complicated, because Excel will do things like interpret values of one type (e.g. string) as being a certain other data type (e.g. date) and then automatically change the display format based on this. Your safest bet it do everything explicitly and not to rely on this automatic stuff.

I ran your experiment and I don't get the same results as you do. My cell A1 stays a Date the whole time, and B1 stays 41575. So I can't answer your question #1. Results probably depend on how your Excel version/settings choose to automatically detect/change a cell's number format based on its content.

Question #2, "How can I ensure that a cell will return a date value": well, not sure what you mean by "return" a date value, but if you want it to contain a numerical value that is displayed as a date, based on what you write to it from VBA, then you can either:

• Write to the cell a string value that you hope Excel will automatically interpret as a date and format as such. Cross fingers. Obviously this is not very robust. Or,

• Write a numerical value to the cell from VBA (obviously a Date type is the intended type, but an Integer, Long, Single, or Double could do as well) and explicitly set the cells' number format to your desired date format using the .NumberFormat property (or manually in Excel). This is much more robust.

If you want to check that existing cell contents can be displayed as a date, then here's a function that will help:

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 0End Function

Example usage:

Dim cell As RangeSet cell = Range("A1")If CellContentCanBeInterpretedAsADate(cell) Then    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/13ActiveCell.NumberFormat = "dd/mm/yyyy" ' Outputs 28/10/2013

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 0End Function

Example usage:

Dim cell As Rangedim cvalue as doubleSet 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