{"id":18466,"date":"2022-01-15T18:23:01","date_gmt":"2022-01-15T17:23:01","guid":{"rendered":"https:\/\/ekiwi-blog.de\/18466\/vba-excel-als-datum-formatieren\/"},"modified":"2022-07-30T10:25:54","modified_gmt":"2022-07-30T09:25:54","slug":"vba-excel-als-datum-formatieren","status":"publish","type":"post","link":"https:\/\/ekiwi-blog.de\/en\/18466\/vba-excel-als-datum-formatieren\/","title":{"rendered":"VBA Excel format cell as date"},"content":{"rendered":"

If you work a lot with VBA<\/abbr>, you can make a lot of work easier for yourself in office programs, such as Excel, by automating routine tasks.
\nOne 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:<\/p>\n

Sheets(\"Sheet1\").Cells(5, 3).Value = \"12.01.2022\"<\/code><\/pre>\n
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.<\/p>\n
It would be very tedious and against the actual work relief, if you now manually change the relevant cell format to “date<\/em>” by right-clicking => Format cells<\/em>.<\/p>\n
Convert to datatype Date in VBA<\/h2>\n
In order for the Excel cell to automatically adopt the date formatting, you have to use the datatype Date in the VBA<\/a>. If you do not have the value as this type, then you must try to convert it to Date.<\/p>\n
To do this, use the function CDate(…)<\/strong>.<\/p>\n
The following code example illustrates the use of the CDate<\/em> function and the difference in formatting a date as String<\/em> and as Date<\/em> and its effect on the cell formatting in Excel.<\/p>\n
We used here the german or european notation of a date. That can differ from your language settings in your window installation.<\/p>\n
Sub<\/span> InsertDateString()\r\nDim<\/span> MyDateString As String<\/span>\r\nDim<\/span> MyDate As Date<\/span>\r\n\r\nMyDateString = \"12.01.2022\"\r\n\r\nOn Error GoTo<\/span> ErrorMessage:\r\n    MyDate = CDate<\/span>(MyDateString )\r\n\r\n    Sheets(\"Sheet1\").Cells(5, 3).Value = MyDateString\r\n    Sheets(\"Sheet1\").Cells(7, 3).Value = MyDate\r\nExit Sub<\/span>\r\nErrorMessage:\r\n    MsgBox \"Error: Could not convert date\"\r\nEnd\r\nEnd Sub<\/span><\/code><\/pre>\n
If this macro is used, the date “12.01.2022<\/em>” is entered once as normal text in the Excel cell C5<\/strong>. The formatting of the cell is then set to General.<\/p>\n
\"Screenshot
Date entry does not have the correct formatting as date<\/figcaption><\/figure>\n
In the other Excel cell C7<\/strong>, the date is inserted using VBA with the data type Date<\/em>, after the CDate()<\/em> function has been applied. In this case, the cell formatting<\/a> in Excel also fits immediately.<\/p>\n
\"Screenshot
Excel cell is set to the correct data type “date<\/em>“.<\/figcaption><\/figure>\n
So you can see that the use of the Date<\/em> data type in the VBA code is crucial for the correct formatting of the cell type in Excel.<\/p>\n
Since the conversion by means of CDate()<\/em> 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<\/strong>” and output an error message via MsgBox<\/strong> like shown in the above code.<\/p>\n
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<\/a>.<\/p>","protected":false},"excerpt":{"rendered":"
If you work a lot with VBA, you can make a lot of work easier for yourself in office programs,<\/p>\n","protected":false},"author":2,"featured_media":13630,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1555],"tags":[1852,1853,1854,1702,1855,1856,1703,1857,1858,1704,1327,1859,1860,1700,1705,1861,1706,1862],"class_list":["post-18466","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-software-en","tag-convert-en","tag-date-en","tag-datum-en","tag-excel-en","tag-excel-en-2","tag-konvertieren-en","tag-microsoft-en","tag-microsoft-en-2","tag-microsoft-office-en-2","tag-microsoft-office-en","tag-microsoft-office","tag-programmieren-en","tag-programmierung-en","tag-programming","tag-vba-en","tag-vba-en-2","tag-visual-basic-for-application-en","tag-visual-basic-for-application-en-2"],"_links":{"self":[{"href":"https:\/\/ekiwi-blog.de\/en\/wp-json\/wp\/v2\/posts\/18466","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/ekiwi-blog.de\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/ekiwi-blog.de\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/ekiwi-blog.de\/en\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/ekiwi-blog.de\/en\/wp-json\/wp\/v2\/comments?post=18466"}],"version-history":[{"count":0,"href":"https:\/\/ekiwi-blog.de\/en\/wp-json\/wp\/v2\/posts\/18466\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/ekiwi-blog.de\/en\/wp-json\/wp\/v2\/media\/13630"}],"wp:attachment":[{"href":"https:\/\/ekiwi-blog.de\/en\/wp-json\/wp\/v2\/media?parent=18466"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/ekiwi-blog.de\/en\/wp-json\/wp\/v2\/categories?post=18466"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/ekiwi-blog.de\/en\/wp-json\/wp\/v2\/tags?post=18466"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}