Format Date and Time

System-defined Format is dependent on your System's Locale settings:

System Locale controls the language used when displaying text in programs tha do not support Unicode. Applications affected by this setting are only those that do not use Unicode as their default character-encoding mechanism and hence this setting is not applicable to applications that are already Unicode-encoded. The system locale is a unique setting for each system and the administrator has to change it manually wherein the computer is required to be restarted for the changes to take effect. System locale can be set from the Administrative tab of the Region item in Control Panel in Windows 8.

The User Locale determines which default settings a user wants for formatting Numbers, Currency, Time & Date. The user locale can be set or changed by the individual user manually from the Formats tab of the Region item in Control Panel in Windows 8. Locale-aware applications use this value to display formatted data.

Your System's Locale settings (User Locale) determines display format for Numbers, Currency, Time & Date by running your code whereas the short time format and short date format of your Code Locale (which is always English/U.S. in Visual Basic) determines display of Time & Date in the development environment.

Example: Using Predefined Named Date and Time formats - System Settings - English (United States)

Sub PredefinedNamedDateAndTimeFormats_1()

'Using Predefined Named Date and Time formats.

'Your system setting determines the Date and Time display for General Date, Long Date, Short Date & Long Time;

'Medium Date format is per the language version of your host Excel application;

'Short Time Format uses a 24-hour format, displays hours & minutes;

'SYSTEM SETTINGS SPECIFIED IN THE COMPUTER'S REGIONAL SETTINGS - English (United States):

'Long Time Format = h:mm:ss tt

'Long Date Format = dddd, MMMM d, yyyy

'Short Time Format = h:mm tt

'Short Date Format = M/d/yyyy

'M = month; m = minute

'AM & PM Symbol = AM & PM

'tt = AM Or PM

Dim MyDate As Variant, MyTime As Variant, SerialNo As Variant, str As Variant

SerialNo = "41851.87"

MyDate = #7/31/2014#

MyTime = #7:16:32 PM#

'returns "7/31/2014 8:52:48 PM"

str = Format(SerialNo, "General Date")

MsgBox str

'returns "Thursday, July 31, 2014"

str = Format(MyDate, "Long Date")

MsgBox str

'returns "31-July-14"

str = Format(MyDate, "Medium Date")

MsgBox str

'returns "7/31/2014"

str = Format(MyDate, "Short Date")

MsgBox str

'returns "7:16:32 PM"

str = Format(MyTime, "Long Time")

MsgBox str

'returns "07:16 PM"

str = Format(MyTime, "Medium Time")

MsgBox str

'returns "19:16"

str = Format(MyTime, "Short Time")

MsgBox str

End Sub

Example: Using Predefined Named Date and Time formats - System Settings - English (United Kingdom)

Sub PredefinedNamedDateAndTimeFormats_2()

'Using Predefined Named Date and Time formats.

'Your system setting determines the Date and Time display for General Date, Long Date, Short Date & Long Time;

'Medium Date format is per the language version of your host Excel application;

'Short Time Format uses a 24-hour format, displays hours & minutes;

'SYSTEM SETTINGS SPECIFIED IN THE COMPUTER'S REGIONAL SETTINGS - English (United Kingdom):

'Long Time Format = HH:mm:ss

'Long Date Format = dd MMMM yyyy

'Short Time Format = HH:mm

'Short Date Format = dd/MM/yyyy

'h/H = 12/24 hour

'M = month; m = minute

Dim MyDate As Variant, MyTime As Variant, SerialNo As Variant, str As Variant

SerialNo = "41851.87"

MyDate = #7/31/2014#

MyTime = #7:16:32 PM#

'returns "31/07/2014 20:52:48"

str = Format(SerialNo, "General Date")

MsgBox str

'returns "31 July 2014"

str = Format(MyDate, "Long Date")

MsgBox str

'returns "31-July-14"

str = Format(MyDate, "Medium Date")

MsgBox str

'returns "31/07/2014"

str = Format(MyDate, "Short Date")

MsgBox str

'returns "19:16:32"

str = Format(MyTime, "Long Time")

MsgBox str

'returns "07:16 PM"

str = Format(MyTime, "Medium Time")

MsgBox str

'returns "19:16"

str = Format(MyTime, "Short Time")

MsgBox str

End Sub

Named Numeric Formats

Listed below are predefined numeric format names.

Format Function in VBA

The vba Format function formats and returns an expression (String value) as per the specified format. Syntax: Format(expression,format,firstdayofweek,firstweekofyear). It is necessary to specify the expression argument, which is any expression you wish to format. The other 3 arguments are optional to specify. The format argument specifies a named or user-defined format expression - (i) to format numbers (or dates/times), you will use predefined named numeric (or date/time) formats or create user-defined numeric (or date/time) formats; (ii) to format date and time serial numbers, you will use date/time formats or numeric formats; (iii) to format strings you will create user-defined string formats. You can also use constants to specify the first day of the week or the first week of the year in the arguments of firstdayofweek and firstweekofyear respectively.

Formatting a number by omitting the format argument will return a String representation of the number, similar to using the Str vba function, but a positive number thus formatted will not include a leading space for the sign of number in the returned string like when using the Str function.

While using the Format Function for Date formatting, you must specify the expression argument appropriately in Gregorian or Hijri depending if your Calendar property setting is Gregorian or Hijri.

Constants to be used for the argument firstdayofweek: vbUseSystem (value 0) - use NLS API setting; vbSunday (this is the default) (value 1) - Sunday; vbMonday (value 2) - Monday; vbTuesday (value 3) - Tuesday; vbWednesday (value 4) - Wednesday; vbThursday (value 5) - Thursday; vbFriday (value 6) - Friday; vbSaturday ( value 7) - Saturday.

Constants to be used for the argument firstweekofyear: vbUseSystem (value 0) - use NLS API setting; vbFirstJan1 (this is the default) (value 1) - start with week in which January 1 occurs; vbFirstFourDays (value 2) - start with the first week which has at least four days in the year; vbFirstFullWeek (value 3) - start with the first full week of the year which has all 7 days.

Named Date/Time Formats

Listed below are predefined named Date and Time formats. Your system setting determines the Date display.

Example: Using Predefined Named Numeric formats.

Sub NamedNumericFormats()

'Using Predefined Named Numeric formats.

Dim str As Variant

'returns "5678.9523"

str = Format(5678.9523, "General Number")

MsgBox str

'returns "$5,678.95"

str = Format(5678.9523, "Currency")

MsgBox str

'returns "5678.90"

str = Format(5678.9, "Fixed")

MsgBox str

'returns "5,678.95"

str = Format(5678.9523, "Standard")

MsgBox str

'returns "595.23%"

str = Format(5.9523, "Percent")

MsgBox str

'returns "5.68E+03"

str = Format(5678.9523, "Scientific")

MsgBox str

'returns "Yes"

str = Format(-5.95, "Yes/No")

MsgBox str

'returns "True"

str = Format(5.95, "True/False")

MsgBox str

'returns "Off"

str = Format(0, "On/Off")

MsgBox str

End Sub

Characters used to create User-Defined Number Formats with the VBA Format Function

Example: Using User-defined String formats with the VBA Format Function.

Sub UserDefinedStringFormats()

'Using User-defined String formats with the VBA Format Function.

Dim str As Variant

'returns " Excel" & 7 - (2 leading spaces appear on the left of "Excel")

str = Format("Excel", "@@@@@@@")

MsgBox str

MsgBox Len(str)

'returns "Excel " & 7 - (2 leading spaces appear on the right of "Excel")

str = Format("Excel", "!@@@@@@@")

MsgBox str

MsgBox Len(str)

'returns "Mr James" - Precede String with "Mr "

str = Format("James", """Mr ""@")

MsgBox str

'returns "Hello Tracy" - Precede String with "Hello "

str = Format("Tracy", "\H\e\l\l\o @")

MsgBox str

'returns "EXCEL" - Displays all characters as uppercase.

str = Format("Excel", ">")

MsgBox str

'returns "excel" - Displays all characters as lowercase.

str = Format("Excel", "<")

MsgBox str

End Sub

VBA Format Function can have One or Two Sections for User-defined Formats, for different Formats for different String Values

While creating User-Defined String Formats with the VBA Format Function, you can have One or Two sections, each section being separated by semicolons(;), wherein you can create different formats for different String values. If only one section is specified, it applies to all string data; if two sections are specified, the first applies applies to string data, and the second section applies to Null values and zero-length strings ("").

Example: Using User-defined Date & Time formats with the VBA Format Function.

Sub UserDefinedDateTimeFormats()

'returns the Date and Time per user defined format

Dim MyDate As Variant, MyTime As Variant, str As Variant

MyDate = #7/1/2014#

MyTime = #7:09:32 PM#

'returns "07/01/2014"

str = Format(MyDate, "mm/dd/yyyy")

MsgBox str

'returns "07/08/2013"

str = Format(41463, "mm/dd/yyyy")

MsgBox str

'returns "7/8/13"

str = Format(41463, "m/d/yy")

MsgBox str

'returns "7/8/2013 6:01:26 AM" - Date is displayed as ddddd and time is displayed as ttttt, in that order.

str = Format(41463.251, "c")

MsgBox str

'returns "6:01:26 AM" - only time information is displayed if no integer portion.

str = Format(0.251, "c")

MsgBox str

'returns "2" - Day of the week is displayed as a number (1-7 for Sunday-Saturday).

str = Format(41463, "w")

MsgBox str

'returns "28" - Week of the year is displayed as a number (1 to 54).

str = Format(#7/8/2013#, "ww")

MsgBox str

'returns "3" - Quarter of the year is displayed as a number (1 to 4)

str = Format(#7/8/2013#, "q")

MsgBox str

'returns "189" - Day of the year is displayed as a number (1 to 366)

str = Format(#7/8/2013#, "y")

MsgBox str

'returns "7/1/14"

str = Format(MyDate, "m/d/yy")

MsgBox str

'returns "Tue, Jul 1, 2014"

str = Format(MyDate, "ddd, mmm d, yyyy")

MsgBox str

'returns "19:9:32"

str = Format(MyTime, "h:n:s")

MsgBox str

'returns "07:09:32 pm"

str = Format(MyTime, "hh:mm:ss am/pm")

MsgBox str

'returns "07:09:32 PM"

str = Format(MyTime, "hh:mm:ss AMPM")

MsgBox str

'returns "06:1:26 A"

str = Format(0.251, "Hh:n:ss A/P")

MsgBox str

'returns "6:09:05 AM" - Complete time is displayed (including hour, minute, and second)

str = Format(0.25631, "ttttt")

MsgBox str

'omitting the format argument will return a String representation of the number - "245"

str = Format(245)

MsgBox str

End Sub

Characters used to create User-Defined String Formats with the VBA Format Function

Example: Using User-defined Number formats with the VBA Format Function.

Sub UserDefinedNumberFormats()

'Using User-defined Number formats with the VBA Format Function.

Dim str As Variant

'returns "5678.95"

str = Format(5678.9523, "0.00")

MsgBox str

'returns "05.90"

str = Format(5.9, "00.00")

MsgBox str

'returns "5.9"

str = Format(5.9, "##.##")

MsgBox str

'returns "5.95"

str = Format(5.9523, "##.00")

MsgBox str

'returns "5679"

str = Format(5678.9523, "#")

MsgBox str

'returns "$5672.5"

str = Format(5672.4523, "##.#")

MsgBox str

'returns "5,678.95"

str = Format(5678.9523, "#,###.##")

MsgBox str

'returns "(456) 789 - 1234"

str = Format(4567891234#, "(###) ### - ####")

MsgBox str

'returns ".63%"

str = Format(0.00625, "#.##%")

MsgBox str

'returns "%1"

str = Format(0.625, "%#")

MsgBox str

'returns "4.56E+6"

str = Format(4560000, "0.00E+0")

MsgBox str

'returns "45.6E+05"

str = Format(4560000, "#0.0E+00")

MsgBox str

'returns "12E4"

str = Format(123456, "##E-0")

MsgBox str

'returns "12E+4"

str = Format(123456, "##E+0")

MsgBox str

'returns "$ +1,456.70"

str = Format(1456.7, "$ +#,###.00")

MsgBox str

'returns "Hello Tracy"

str = Format("Tracy", "\H\e\l\l\o @")

MsgBox str

'returns "75 kg"

str = Format("75", "0 kg")

MsgBox str

End Sub

VBA Format Function can have upto Four Sections for User-defined Formats, for different Formats for different Numeric Values

While creating User-Defined Number Formats with the VBA Format Function, you can have upto 4 sections, each section being separated by semicolons(;), wherein you can create different formats for different Numeric values. Creating multiple sections is not possible wherein the format argument contains any Predefined Named Numeric format.

There can be upto four sections of format argument, wherein each section is separated by a semicolon. These sections determine the display of positive values, negative values, zeros and Null values, in that order. If only one section is specified, it applies to all values; if two sections are specified, the first applies to positive and zero values and the second section applies to negative values. When you use 3 sections for format argument, the first section applies to positive values, the second section applies to negative values, and the third section applies to zeros.

Example: Using multiple sections for user-defined Number formats with the VBA Format Function.

Sub MultipleSectionsUserDefinedNumberFormats()

'Using multiple sections for user-defined Number formats with the VBA Format Function.

Dim str As Variant

'returns "5,678.952"

str = Format(5678.9523, "#,###.000;($ #,##0);0.00;Null")

MsgBox str

'returns "($ 5,679)"

str = Format(-5678.9523, "#,###.000;($ #,##0);0.00;Null")

MsgBox str

'returns "0.00"

str = Format(0, "#,###.000;($ #,##0);0.00;Null")

MsgBox str

End Sub

Missing a Section:

You can also skip a section and specify format argument for the following or preceding section, but then you must enter the ending semicolon for the skipped section. The skipped section is printed using the format of the positive value, and in the absence of a format for a positive value, skipping a section will result in a blank display for that section. See below example.

Example: Missing sections for user-defined Number formats with the VBA Format Function.

Sub MissingSectionsUserDefinedNumberFormats()

'missing sections for user-defined Number formats with the VBA Format Function.

Dim str As Variant

'returns "" ie. blank (positive expression, but no format argument for positive value)

str = Format(5678.9523, ";($ #,##0);Zero;")

MsgBox str

'returns "-5,678.952" (for negative expression, using the format of the positive value when no format argument for negative value)

str = Format(-5678.9523, "#,###.000;;Zero")

MsgBox str

'returns "" ie. blank (negative expression, no format argument for positive or negative values)

str = Format(-5678.9523, ";;")

MsgBox str

'returns "($ 5,679)" (for negative expression, using format argument for negative values)

str = Format(-5678.9523, ";($ #,##0);")

MsgBox str

'returns "zero" (for zero expression, using format argument for zero values)

str = Format(0, ";;\z\e\r\o")

MsgBox str

'returns ".000" (for zero expression, using the format of the positive value)

str = Format(0, "#,###.000;;")

MsgBox str

End Sub

Characters used to create User-Defined Date & Time Formats with the VBA Format Function

Page updated

Google Sites

Report abuse