VBA Excel format cell as date

If you work a lot with VBA, you can make a lot of work easier for yourself in office programs, such as Excel, by automating routine tasks.
One problem can be that you want to insert a value, which represents a date, into an Excel cell using VBA. The insertion is relatively simple with the following code:

Sheets("Sheet1").Cells(5, 3).Value = "12.01.2022"

The problem is that the value is then contained in the relevant Excel cell and is also readable as long as no other formatting interferes. However, the date value is not formatted as a Date-type in Excel cell, so that it is not possible to perform any special date-functions in Excel, such as calculating with the date or determining the day of the week.

It would be very tedious and against the actual work relief, if you now manually change the relevant cell format to “date” by right-clicking => Format cells.

Convert to datatype Date in VBA

In order for the Excel cell to automatically adopt the date formatting, you have to use the datatype Date in the VBA. If you do not have the value as this type, then you must try to convert it to Date.

To do this, use the function CDate(…).

The following code example illustrates the use of the CDate function and the difference in formatting a date as String and as Date and its effect on the cell formatting in Excel.

We used here the german or european notation of a date. That can differ from your language settings in your window installation.

Sub InsertDateString()
Dim MyDateString As String
Dim MyDate As Date

MyDateString = "12.01.2022"

On Error GoTo ErrorMessage:
    MyDate = CDate(MyDateString )

    Sheets("Sheet1").Cells(5, 3).Value = MyDateString
    Sheets("Sheet1").Cells(7, 3).Value = MyDate
Exit Sub
ErrorMessage:
    MsgBox "Error: Could not convert date"
End
End Sub

If this macro is used, the date “12.01.2022” is entered once as normal text in the Excel cell C5. The formatting of the cell is then set to General.

In the other Excel cell C7, the date is inserted using VBA with the data type Date, after the CDate() function has been applied. In this case, the cell formatting in Excel also fits immediately.

So you can see that the use of the Date data type in the VBA code is crucial for the correct formatting of the cell type in Excel.

Since the conversion by means of CDate() can lead to an exception (program abort) if the passed value cannot be converted into a date, you should catch this exception with “On Error GoTo” and output an error message via MsgBox like shown in the above code.

By the way it is always good to comment your code but in VBA editor it is tricky to comment a code block of multiple lines.