Skip to main content

Format function

Returns a Variant (String) containing an expression formatted according to instructions contained in a format expression.

Syntax

Format(Expression, [ Format ], [ FirstDayOfWeek ], [ FirstWeekOfYear ])

The Format function syntax has these parts.

Part Description
Expression Required. Any valid expression.
Format Optional. A valid named or user-defined format expression.
FirstDayOfWeek Optional. A constant that specifies the first day of the week.
FirstWeekOfYear Optional. A constant that specifies the first week of the year.

Settings

The firstdayofweek argument has these settings.

Constant Value Description
vbUseSystem 0 Use NLS API setting.
vbSunday 1 Sunday (default)
vbMonday 2 Monday
vbTuesday 3 Tuesday
vbWednesday 4 Wednesday
vbThursday 5 Thursday
vbFriday 6 Friday
vbSaturday 7 Saturday

The firstweekofyear argument has these settings.

Constant Value Description
vbUseSystem 0 Use NLS API setting.
vbFirstJan1 1 Start with week in which January 1 occurs (default).
vbFirstFourDays 2 Start with the first week that has at least four days in the year.
vbFirstFullWeek 3 Start with the first full week of the year.

Remarks

To format Do this
Numbers Use predefined named numeric formats or create user-defined numeric formats.
Dates and times Use predefined named date/time formats or create user-defined date/time formats.
Date and time serial numbers Use date and time formats or numeric formats.
Strings Create your own user-defined string formats.

Format truncates format to 257 characters.

If you try to format a number without specifying format, Format provides functionality similar to the Str function, although it is internationally aware. However, positive numbers formatted as strings using Format don't include a leading space reserved for the sign of the value; those converted using Str retain the leading space.

If you are formatting a non-localized numeric string, you should use a user-defined numeric format to ensure that you get the look you want.

If the calendar is Gregorian, the meaning of format expression symbols is unchanged. If the calendar is Hijri, all date format symbols (for example, dddd, mmmm, yyyy ) have the same meaning but apply to the Hijri calendar. Format symbols remain in English; symbols that result in text display (for example, AM and PM) display the string (English or Arabic) associated with that symbol. The range of certain symbols changes when the calendar is Hijri.

Date symbols

Symbol Range
d 1-31 (Day of month, with no leading zero)
dd 01-31 (Day of month, with a leading zero)
w 1-7 (Day of week, starting with Sunday = 1)
ww 1-53 (Week of year, with no leading zero; Week 1 starts on Jan 1)
m 1-12 (Month of year, with no leading zero, starting with January = 1)
mm 01-12 (Month of year, with a leading zero, starting with January = 01)
mmm Displays abbreviated month names (Hijri month names have no abbreviations)
mmmm Displays full month names
y 1-366 (Day of year)
yy 00-99 (Last two digits of year)
yyyy 100-9999 (Three- or Four-digit year)

Time symbols

Symbol Range
h 0-23 (1-12 with "AM" or "PM" appended) (Hour of day, with no leading zero)
hh 00-23 (01-12 with "AM" or "PM" appended) (Hour of day, with a leading zero)
n 0-59 (Minute of hour, with no leading zero)
nn 00-59 (Minute of hour, with a leading zero)
m 0-59 (Minute of hour, with no leading zero). Only if preceded by h or hh
mm 00-59 (Minute of hour, with a leading zero). Only if preceded by h or hh
s 0-59 (Second of minute, with no leading zero)
ss 00-59 (Second of minute, with a leading zero)

Example

This example shows various uses of the Format function to format values using both named formats and user-defined formats. For the date separator (/), time separator (:), and AM/ PM literal, the actual formatted output displayed by your system depends on the locale settings on which the code is running. When times and dates are displayed in the development environment, the short time format and short date format of the code locale are used. When displayed by running code, the short time format and short date format of the system locale are used, which may differ from the code locale. For this example, English/U.S. is assumed. MyTime and MyDate are displayed in the development environment using current system short time setting and short date setting.

Dim MyTime, MyDate, MyStr
MyTime = #17:04:23#
MyDate = #January 27, 1993#

' Returns current system time in the system-defined long time format.
MyStr = Format(Time, "Long Time")

' Returns current system date in the system-defined long date format.
MyStr = Format(Date, "Long Date")

MyStr = Format(MyTime, "h:m:s")    ' Returns "17:4:23".
MyStr = Format(MyTime, "hh:mm:ss am/pm")    ' Returns "05:04:23 pm".
MyStr = Format(MyTime, "hh:mm:ss AM/PM")    ' Returns "05:04:23 PM".
MyStr = Format(MyDate, "dddd, mmm d yyyy")    ' Returns "Wednesday, Jan 27 1993".
' If format is not supplied, a string is returned.
MyStr = Format(23)    ' Returns "23".

' User-defined formats.
MyStr = Format(5459.4, "##,##0.00")    ' Returns "5,459.40".
MyStr = Format(334.9, "###0.00")    ' Returns "334.90".
MyStr = Format(5, "0.00%")    ' Returns "500.00%".
MyStr = Format("HELLO", "<") ' Returns "hello". MyStr = Format("This is it", ">")    ' Returns "THIS IS IT".

Leave a comment

Your email address will not be published. Required fields are marked *

Format your code: <pre><code class="language-vba">place your code here</code></pre>