Normal. VBA format function returns or results in a formatted string from a string expression. The image shows the version of the sample Format_Dates macro that does this:Let's go back to the sample table with the schedule of the 2014 World Cup.
Excel stores date as serial numbers and if you apply date format it will show accordingly. This has been a guide to VBA Format Date. You can also To finish this blog post, I introduce one such date formatting macro:You can add a carriage return in custom date formats. In this article, we will show you different techniques for formatting dates with VBA codes.To change the date format with VBA coding, we need to be aware of what the date formats are and its impact on the date.The below table shows the different date formatting and their codes.So, if you keep in mind the above chart formatting the date through For example, we have the same date in multiple cells of the worksheet as shown below.Now we will apply different date formats for the same date to see the impact at different date format codes.First, copy the same data to the next column as well to see the impact.In the code first, we need to select the cell by using the Since we are changing the date format of the cell we need to access the “When we execute this code it will apply the number format to the cell Similarly, I have applied different formatting codes for other cells as well and below is the VBA code for you.In VBA we have a function called FORMAT which can be used to apply the desired format to the cell.We just need to specify what the value is for “Expression” and apply the “Format” accordingly.In the above code, I have defined the variable as variant (which can hold any value).Next for this variable, I have assigned the value as 43586.Next in the message box, I have shown the result of the variable, but before we show the result we have used the “Now you must be wondering we have supplied serial number but result showing as the date. I use this statement in other sample macros throughout Power Spreadsheets, including The first statement within the With…End With block is:Since, as explained above, this statement works with the object returned by the Application.Selection property, it's the equivalent of:Most of this statement follows exactly the same structure of (pretty much) all of the other macro examples I include in the previous sections. This corresponds to the World Cup match between France and Honduras.The following image shows the results of executing the sample Format_Dates macro while this cell is active:The second way in which you can format a date to display only the year results in Excel showing the full year. Let's assume that, before applying this new version of the Format_Dates macro, all of the dates have the long date format, as shown in the following screenshot:Before executing the Format_Dates macro, I select the cell that I want to format. That means you can delete “[$-en-US]”. The Range.NumberFormat Property And How To Format An Excel Date Using VBA. This corresponds to June 12 of 2014, which is the date of the match between Brazil and Croatia.Once I execute the version above of the Format_Dates macro, the date format changes to the following:The 5-step method to find date format codes described above can be useful in some situations.Let's take a look at these general rules and some additional examples:The date format codes that you can use to format a date using VBA appear in the table below. In such a case, simply Once you've selected the Date category, Excel displays the built-in date format types inside the Type box on the right side of the Format Cells dialog box. I am trying to insert the date into a letter as part of a larger macro. In other words:The second statement within the With…End With block is also new. The date is June 13 of 2014. The only property of the Range object that I cover in this blog post is NumberFormat. The formatted date is June 20 of 2014, corresponding to the World Cup match between Switzerland and France.This example works with a date format that only includes month (using the format code mmmm) and year (using the format code yyyy). Such a long list of examples may seem a little excessive, and there are several similarities between some of the date formats I applied.This Excel VBA Date Format Tutorial is accompanied by an Excel workbook containing the data and some versions of the macros I explain above. The first image shows what happens when the Format_Dates_Carriage_Return macro is executed without having the statement under analysis. Suppose, I have the date value Save your workbook as “Excel macro-enabled workbook”.