Date Format in VBA
On a daily basis, we usually format dates, VBA format function helps you out to format date into different date types and its appearance as per your requirement. VBA format function is categorized under Text/String function. VBA format function returns or results in a formatted string from a string expression. In VB macro window, when you click or enter CTRL + SPACE, VBA Intellisense drop-down menu appears which helps you out in writing the code & you can autocomplete words in the VB editor.
Syntax of VBA Date Format in Excel
The syntax for VBA Date format function in excel is as follows:
Parameters of Date Format in VBA
It contains one required or compulsory parameter and the remaining three as optional parameters.
- Expression (Compulsory or required argument): It represents or indicates a date value which you want to format.
- [Format]: It is a format, which you want to apply to the Expression argument that you have selected.
- [FirstDayOfWeek] (optional argument): A constant that specifies the first day of the week.
If left blank or argument is not entered then it uses the default value vbSunday (Sunday).
[FirstWeekOfYear] (optional argument): A constant that specifies the first week of the year.
It represents or indicates the first week of the year. If this argument left blank or argument not entered then uses the default value vbFirstJan1 (1st January).
Various Types of Pre-defined Date Format
- General Date
- Short Date
- Medium Date
- Long Date
Different User Defined Date Format that can be Used to Set the Date Value
How to Use VBA Date Format in Excel?
Below are the different examples to use VBA Date Format Function in Excel.
VBA Date Format – Example #1
Step 1: Go to the Developer’s tab and click on Visual Basic.
Step 2: Open a Module from the Insert menu option as shown below.
Step 3: To create a blank module, Under the Microsoft excel objects, right-click on sheet 1 (VB_DATE_FORMAT) Insert and under the menu section select Module, so that a new blank module gets created.
VBA Date Format – Example #2
Now the blank module is created, it is also called a code window, where you can start writing VBA DATE FORMAT function statement codes. Suppose, I have the date value “April 12th 2019” let’s apply VB different date format to it
Step 1: Open a Module from the Insert menu option as shown below.
Step 2: As VB format function is categorized under text/string type Variables, DIM (Dimension) is used in a VBA code to declare a variable name and its type. therefore, here, I defined the variable as “string” data type.
Code:
Sub VBA_FORMAT_SHORT_DATE() Dim A As String End Sub
Step 3: I have used range function after format function to obtain output in a specific cell, the final code format to be used for a short date is:
Code:
Sub VBA_FORMAT_SHORT_DATE() Dim A As String A = 4 - 12 - 2019 A = Format("04 - 12 - 2019", "short date") Range("G6") = A End Sub
Step 4: If you run the above code, it results in the below-mentioned output in an excel sheet. similarly, you can test different date format (short or medium), based on your choice (other examples explained in a macro window).
VBA Date Format – Example #3
Step 1: In this example, you can create a date value format of your choice i.e. User-defined date formats.
Code:
Sub TODAY_DATE() Dim date_example As String date_example = Now() Range("D6") = Format(date_example, "dd") Range("D7") = Format(date_example, "ddd") Range("D8") = Format(date_example, "dddd") Range("D9") = Format(date_example, "m") Range("D10") = Format(date_example, "mm") Range("D11") = Format(date_example, "mmm") Range("D12") = Format(date_example, "mmmm") Range("D13") = Format(date_example, "yy") Range("D14") = Format(date_example, "yyyy") Range("D15") = Format(date_example, "w") Range("D16") = Format(date_example, "ww") Range("D17") = Format(date_example, "q") End Sub
Step 2: In the above-mentioned code, format function is applied to different user-defined date format’s for Today’s date, if I run the above code, it will result in the below-mentioned output in a respective cell range.
Save your workbook as “Excel macro-enabled workbook”. By clicking on save as at the left corner of the worksheet. “Save as” window popup appears, now you can save this file as Excel Macro enabled workbook under save as type.
Once again if you open a file, you can click on shortcut key i.e. Fn + Alt +F8, “Macro” dialog box appears, where you can run a saved macro code of your choice or you can click on Fn + Alt + F11 for a full macro window
Things to Remember
- It can be used either as procedure or function in a VBA editor window to format date.
- Usually, Excel stores all dates as integers.
- In the Format function argument, Date value needs to be supplied in double-quotes to get the correct result.
Recommended Articles
This is a guide to VBA Date Format Function. Here we discuss How to use VBA Date Format Function in Excel along with a few practical examples and downloadable excel template. You can also go through our other suggested articles –
