Formatting Data

Format Predefined Date

The following predefined date formats may be used with the Format function. Predefined formats may not be combined with user defined formats or other predefined formats.

Form	Description
General Date	Same as user defined date format "c"
Long Date	Same as user defined date format "dddddd"
Medium Date	Not supported at this time.
Short Date	Same as user defined date format "ddddd"
Long Time	Same as user defined date format "ttttt"
Medium Time	Same as user defined date format "hh:mm AMPM"
Short Time	Same as user defined date format "hh:mm"

Format Predefined Number

The following predefined number formats may be used with the Format function. Predefined formats may not be combined with user defined formats or other predefined formats.

Form	Description
General Number	Returns number as is.
Currency	Same as user defined number format "$#,##0.00;($#,##0.00)" (Not locale dependent.)
Fixed	Same as user defined number format "#,##0.00".
Percent	Same as user defined number format "0.00%".
Scientific	Same as user defined number format "0.00E+00".
Yes/No	Returns "No" if zero, else returns "Yes".
True/False	Returns "True" if zero, else returns "False".
On/Off	Returns "On" if zero, else returns "Off".

Example

Sub Main
Debug.Print Format$(2.145,"Standard") ' 2.15
End Sub

Format User Defined Date

The following date formats may be used with the Format function. Date formats may be combined to create the user defined date format. User defined date formats may not be combined with other user defined formats or predefined formats.

Parameters	Description
:	Insert localized time separator.
/	Insert localized date separator.
c	Insert ddddd ttttt, insert date only if t=0, insert time only if d=0.
d	Insert day number without leading zero.
dd	Insert day number with leading zero.
ddd	Insert abbreviated day name.
dddd	Insert full day name.
ddddd	Insert date according to Short Date format.
dddddd	Insert date according to Long Date format.
w	Insert day of week number.
ww	Insert week of year number.
m	Insert month number without leading zero Insert minute number without leading zero (if follows h or hh)
mm	Insert month number with leading zero Insert minute number with leading zero (if follows h or hh)
mmm	Insert abbreviated month name
mmmm	Insert full month name
q	Insert quarter number
y	Insert day of year number
yy	Insert year number (two digits)
yyyy	Insert year number (four digits, no leading zeros)
h	Insert hour number without leading zero
hh	Insert hour number with leading zero
n	Insert minute number without leading zero
nn	Insert minute number with leading zero
s	Insert second number without leading zero
ss	Insert second number with leading zero
ttttt	Insert time according to time format
AM/PM	Use 12 hour clock and insert AM (hours 0 to 11) and PM (12 to 23)
am/pm	Use 12 hour clock and insert am (hours 0 to 11) and pm (12 to 23)
A/P	Use 12 hour clock and insert A (hours 0 to 11) and P (12 to 23)
a/p	Use 12 hour clock and insert a (hours 0 to 11) and p (12 to 23)
AMPM	Use 12 hour clock and insert localized AM/PM strings
\c	Insert character c
"text"	Insert literal text

Format User Defined Number

The following number formats may be used with the Format function. Number formats may be combined to create the user defined number format. User defined number formats may not be combined with other user defined formats or predefined formats.

User defined number formats can contain up to four sections separated by ';':

form - format for non-negative expr, '-'format for negative expr, empty and null expr return ""
form;negform - negform: format for negative expr
form;negform;zeroform - zeroform: format for zero expr
form;negform;zeroform;nullform - nullform: format for null expr

Parameter	Description
#	Digit, don't include leading/trailing zero digits (all the digits left of decimal point are returned). Example: Format(19,"###") returns "19" Example: Format(19,"#") returns "19"
0	Digit, include leading/trailing zero digits. Example: Format(19,"000") returns "019" Example: Format(19,"0") returns "19"
.	Decimal, insert localized decimal point. Example: Format(19.9,"###.00") returns "19.90" Example: Format(19.9,"###.##") returns "19.9"
,	Thousands, insert localized thousand separator every 3 digits "xxx," or "xxx,." mean divide expr by 1000 prior to formatting two adjacent commas ",," means divide expr by 1000 again. Example: Format(1900000,"0,,") returns "2" Example: Format(1900000,"0,,.0") returns "1.9"
%	Percent, insert %, multiply expr by 100 prior to formatting.
:	Returns "No" if zero, else returns "Yes".
/	Insert localized date separator.
E+ e+ E- e-	Use exponential notation, insert E (or e) and the signed exponent. Example: Format(1000,"0.00E+00") returns "1.00E+03" Example: Format(.001,"0.00E+00") returns "1.00E-03"
- + $ ( ) space	Insert literal char Example: Format(10,"$#") returns "$10."
\c	Insert character c. Example: Format(19,"\####\#") returns "#19#"
"text"	Insert literal text. Example: Format(19,"""##""###""##""") returns "##19##"

Example

Sub Main
Debug.Print Format$(2.145,"#.00") ' 2.15
End Sub

Format User Defined Text

The following text formats may be used with the Format function. Text formats may be combined to create the user defined text format. User defined text formats may not be combined with other user defined formats or predefined formats.

User defined text formats can contain one or two sections separated by ';':

form - format for all strings
form;nullform - nullform: format for empty and null strings

Parameter	Description
@	Char placeholder, insert char or space.
&	Char placeholder, insert char or nothing.
<	All chars lowercase.
>	All chars uppercase.
!	Fill placeholder from left-to-right (default is right-to-left).
\c	Insert character c.
"text"	Insert literal text.

Example

Sub Main
Debug.Print Format("123","ab@c") '" ab1c23"
Debug.Print Format("123","!ab@c") '" ab3c"
End Sub