VBA Format Date: Convert It to the Type You Want!
VBA stands for Visual Basic for Applications. It is different from Visual Basic. Microsoft came up with this programming feature to use with the Microsoft Office software suite. It can be used to create programs to be run on Microsoft Excel, Outlook or other components of the MS Office suite. In this intermediate level tutorial, we walk you through VBA Format function for date and time. VBA Format function takes an expression, a date or number value and converts the same into a string. We assume that you know the basic concepts of MS Excel and Visual Basic for Applications. You can learn more about VBA in this excellent course, or of you’re in a hurry, you can take this 24hr VBA trainer course.
How to Assign VBA Date Value to a Variable
You can set the format in any version of Microsoft Excel starting from Excel 2000 to Excel version 2013. You can assign any date format as a value to a variable with the date data type in Excel VBA. However, ensure that values are put between simple hash (#) tags. Here VBA converts the value to a “Date” that can be used in comparisons and other types of statements. The following examples show this:
D1 = #March 24, 2014# D1 = #3/24/2014# D1 = #03/24/2014#
VBA will interpret the above statements to the date “March 24, 2014.” Before we move on, to learn more about how you can use VBA with MS Excel, you may want to try out this primer course; or just crash through our VBA tutorial to brush up your understanding.
VBA Format Function
Microsoft Excel Format function accepts the date expression and returns it as a formatted string. The syntax of Format date function looks like this
Format (expression, [format, [firstdayofweek, [firstweekofyear] ] ] )
Let’s take a closer look at the parameters.
- Expression stands for the value to be formatted.
- Format stands for the format to be applied to the expression. It is optional. In VBA Format Date function you have the option to either define your own format or use some of the MS Excel predefined format.
- Firstdayofweek specifies the first day of the week. If it is not declared, the Format function assumes that Sunday is the first day of the week. This parameter is optional.
- Firstweekofyear is the value that specifies the first week of the year. However, if this parameter is not declared, then the Format function assumes that the first week begins from January 1.
The table given below displays some of the Excel predefined date format.
|
Format |
Explanation |
| General Date | Displays date depending upon the system settings |
| Long Date | Displays date depending upon the system’s long date setting |
| Medium Date | Displays date depending upon the system’s medium date setting |
| Short Date | Displays date depending upon the system’s short date setting |
| Long Time | Displays time depending upon the system’s long time setting |
| Medium Time | Displays time depending upon the system’s medium time setting |
| Short Time | Displays time depending upon the system’s short time setting |
The table given below contains the values the parameter “firstdayoftheweek” can take up in your VBA programs.
|
Constant |
Value |
Explanation |
| vbUseSystem | 0 | Uses the NLS API (National Language Support Application Program Interface) setting |
| VbSunday | 1 | Sunday (default, if parameter is not declared) |
| vbMonday | 2 | Monday |
| vbTuesday | 3 | Tuesday |
| vbWednesday | 4 | Wednesday |
| vbThursday | 5 | Thursday |
| vbFriday | 6 | Friday |
| vbSaturday | 7 | Saturday |
The table below lists the possible values of the optional parameter “Firstweekofyear that you can use in your VBA programs. Take a look
| Constant | Value | Explanation |
| vbUseSystem | 0 | Uses the NLS API setting |
| vbFirstJan1 | 1 | The week that contains January 1 |
| vbFirstFourDays | 2 | The first week that has at least 4 days in the year |
| vbFirstFullWeek | 3 | The first full week of the year |
Here are a few examples of Format function for Date in Excel VBA to make this function more clear.
1. Format(#24/03/2014#, “Short Date”) would return ’24/03/2014′
2. Format(#24/03/2014#, “Long Date”) would return ‘March 24, 2014’
3. Format(#24/03/2014#, “yyyy/mm/dd”) would return ‘2014/03/24’
We suggest that you work out more examples of your own on date function format. That’ll help you get more comfortable with the various options. This course with Mr Excel can help you get a good workout with Excel VBA examples.
User Defined Format Function for date and time
Note that you can also use some pre-defined variants of the Format function for date and time. The syntax of that looks like this
Format (expression, style)
The table below shows the user-defined Format function for date and time. Read through carefully
| Format | Explanation |
| Format (Now, “M”) | Displays the current month and date |
| Format (Now, “MM”) | Displays the current month in double digits. |
| Format (Now, “MMM”) | Displays the abbreviated name of the current month |
| Format (Now, “MMMM”) | Displays the full name of the current month. |
| Format (Now, “dd/MM/yyyy”) | Displays the current date in the day/month/year format. |
| Format (Now, “MMM,d,yyyy”) | Displays the current date in the Month, Day, Year Format |
| Format (Now, “h:mm:ss tt”) | Displays the current time in the format hour:minute:second and show am/pm |
| Format (Now, “MM/dd/yyyy h:mm:ss) | Displays current date and time in the format hour:minute:second |
Let’s take a closer look at the user-defined format function for date and time using a simple VBA program.
Sub Newbutton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click, Button2.Click, Button3.Click Label1.Text = Format(Now, "M") Label2.Text = Format(Now, "MM") Label3.Text = Format(Now, "MMM") Label4.Text = Format(Now, "MMMM") Label5.Text = Format(Now, "dd/MM/yyyy") Label6.Text = Format(Now, "MMM,d,yyyy") Label7.Text = Format(Now, "h:mm:ss tt") Label8.Text = Format(Now, "MM/dd/yyyy h:mm:ss tt") End Sub
When you run this program, it will display the current system date in different formats.
Label1.Text will display March 24 Label2.Text will display 03 Label3.Text will display Mar Label4.Text will display March Label5.Text will display 24/03/2014 Label6.Text will display Mar,24,2014 Label7.Text will display 12:49:23 PM Label8.Text will display 03/24/2014 12:49:23 PM
Characters supported by VBA Date Function
VBA Date functions supports these characters to display date and time. It’s quite a long list, so check out the table below!
| Characters | Example | Description |
| m | 8 | Month (numerical without zeros) |
| mm | 08 | Month (numerical with zeros) |
| mmm | Sep | Month (abbreviated text) |
| mmmm | September | Month (full-length text) |
| d | 6 | Day (numerical without zeros) |
| dd | 06 | Day (numerical with zeros) |
| ddd | Wed | Day (abbreviated text) |
| dddd | Wednesday | Days (full-length text) |
| yy | 14 | Year (last 2 digits) |
| yyyy | 2014 | Year (4 digits) |
| h | 9 | Hours without zeros (0-23) |
| hh | 09 | Hours with zeros (00-23) |
| n | 7 | Minutes without zeros (0-59) |
| nn | 07 | Minutes with zeros (00-59) |
| s | 5 | Seconds without zeros (0-59) |
| ss | 05 | Seconds with zeros (00-59) |
| AM/PM | AM | Display AM/PM |
Let’s use an example to help you to understand the above table better.
Sub date_and_time()
date_example = Now()
Range("C1") = Format(date_example, "mm.dd.yy")
Range("C2") = Format(date_example, "d mmmmyyyy")
Range("C3") = Format(date_example, "mmmm j, yyyy")
Range("C4") = Format(date_example, "ddddd")
Range("C5") = Format(date_example, "mmmm-yy")
Range("C6") = Format(date_example, "mm.dd.yyyyhh:mm")
Range("C7") = Format(date_example, "m.d.yy h:mm AM/PM")
Range("C8") = Format(date_example, "h\Hmm")
End Sub
In this program we assign the system date and time through the now() to the variable date_example. After the execution of the program, the cells will contain the following values
C1 will contain 03.24.14 C2 will contain 24 March 2014 C3 will contain March 24 2014 C4 will contain Mon 24 C5 will contain March-14 C6 will contain 03.24.2014 15:04 C7 will contain 3.24.14 15:04 PM C8 will contain 15H04
Given below is an example of using date function to calculate your age. We assume that you have basic programming knowledge. If not please refer our basic tutorial on VBA programming.
Calculating Age In VBA
Function Age(Date1 As Date, Date2 As Date) As String Dim Year1 As Integer Dim Month_1 As Integer Dim Day1 As Integer Dim Temp As Date Temp = DateSerial(Year(Date2), Month(Date1), Day(Date1)) Year1 = Year(Date2) - Year(Date1) + (Temp > Date2) Month1 = Month(Date2) - Month(Date1) - (12 * (Temp > Date2)) Day1 = Day(Date2) - Day(Date1) If Day1 < 0 Then Month_1 = Month_1 - 1 Day1 = Day(DateSerial(Year(Date2), Month(Date2) + 1, 0)) + Day1 + 1 End If Age = Year1 &“years "& Month_1 & " months " & Day1 & " days" End Function
This program accepts two parameters Date1 and Date2. Date1 is the year of your birth and Date2 is the current date. In the end, you get your age in terms of years, months and days.
Like you’ve just seen, the Format function for date and time comes in variety of options. You can choose the format that you want to display or use in your programs. As always practice makes perfect. Do try these examples for yourself. If you’d like to get more hands on with VBA before you proceed, this course can help you. Once you’re ready to step it up a notch, you can try out this Ultimate VBA course. Let us know how it goes!
Recommended Articles
Excel VBA Select Case: Use VBA to Automatically Make Selections for You!
Udemy Editor
The CStr VBA Function: How to Convert Different Expressions to Strings
Udemy Editor
Top courses in Excel VBA
Excel VBA students also learn
Empower your team. Lead the industry.
Get a subscription to a library of online courses and digital learning tools for your organization with Udemy Business.