Format function (LotusScript® Language)

Formats a number, a date/time, or a string according to a supplied format.

Syntax

Format[$] ( expr [ , fmt ] )

Elements

expr

Any expression. The expression is evaluated as a numeric expression if fmt is a numeric format, as a date/time if fmt is a date/time format, and as a string if fmt is a string format.

fmt

Optional. A format string: either a string consisting of the name of a format pre-defined in LotusScript®, or else a string of format characters. If this format string is not supplied, Format[$] behaves like Str[$], omitting the leading space character for positive numbers.

Return value

Format returns a Variant containing a string, and Format$ returns a String.

If expr is a string and fmt is a numeric format string, LotusScript® attempts to convert the string to a number. If successful, LotusScript® then formats the result.

If the string can't be converted to a number, LotusScript® attempts to interpret it as a date/time, and attempts to convert it to a numeric value. If successful, LotusScript® then formats the result.

If expr can't be converted to the data type of the format string, Format returns expr without formatting it.

Formatting codes

Numeric formats

If expr is numeric, you can use one of the named numeric formats shown in the following section, or create a custom numeric format using the numeric formatting codes shown in the subsequent section.

Named numeric formats

Format name

Display of the value of expr is ...

General Number

As stored, without thousands separators

Currency

As defined in the operating system's international settings. For example, you can format currency values with thousands separators, negative values in parentheses, and two digits that follow the decimal separator.

Fixed

With at least one digit to the left of the decimal separator, and with two digits that follow the decimal separator.

Standard

With thousands separators, with at least one digit to the left of the decimal separator, and with two digits that follow the decimal separator.

Percent

expr multiplied by 100, with at least one digit to the left of the decimal separator. Two digits are displayed following the decimal separator, and a percent sign (%) follows the number.

Scientific

In standard scientific notation: with one digit preceding the decimal separator and two digits following the decimal separator, followed by the letter E or e and a number representing the exponent.

Yes/No

No if the number is 0, and Yes otherwise.

True/False

False if the number is 0, and True otherwise.

On/Off

Off if the number is 0, and On otherwise.

Custom numeric formatting codes

The following table describes the characters you can use in fmt to create custom formats for numeric values.

Formatting code

Meaning

"" (Empty string)

Display the number with no formatting

0 (zero)

Digit forced display. A digit is displayed for each zero in fmt, with leading or trailing zeros to fill unused spaces. All digits to the left of the decimal separator are displayed. If the number includes more decimal places than fmt, it is rounded appropriately.

# (pound sign)

Digit conditional display. The same display as 0 (digit forced display), except that no leading or trailing zeros are displayed.

. (period)

Decimal separator. The position of the decimal separator in fmt. Unless your formatting code includes a 0 immediately to the left of the decimal separator, numbers between -1 and 1 begin with the decimal separator, The actual decimal separator used in the returned formatted value is the decimal separator specified in the operating system's international settings.

% (percent sign)

Percentage placeholder. Multiplies the number by 100 and inserts the percent sign (%) in the position where it appears in fmt. If you include more than one percentage placeholder, the number is multiplied by 100 for each %. For example, %% means multiplication by 10000.

, (comma)

Thousands separator. To separate groups of three digits, counting left from the decimal separator, within numbers that include at least four digits to the left of the decimal separator, enclose the comma between a pair of the digit symbols 0 or #. The actual thousands separator used in the returned formatted value is the thousands separator specified in the operating system's international settings.

A special case is when the comma is placed immediately to the left of the decimal separator (or the position of the implied decimal separator). This causes the number to be divided by 1000. For example, this returns "100":

x = Format$(100000,"##0,.")

If 100000 is replaced in this example by a number less than 1000 in absolute value, then this function returns "0."

E- E+ e- e+

Scientific notation. The number of digit symbols (0 or #) to the left of the decimal separator specifies how many digits are displayed to the left of the decimal separator, and the resulting magnitude of the exponent.

Use E+ or e+ to display the sign of all exponents (the symbol + or -). Use E- or e- to display the sign of negative exponents only (the symbol -).

All exponent digits are displayed, regardless of how many digit symbols follow the E-, E+, e-, or e+. If there are no digit symbols (the symbol 0 or #), an exponent of zero is not displayed; otherwise at least one exponent digit is displayed. Use 0 to format a minimum number of exponent digits, up to a maximum of three.

$ (dollar sign)

Currency symbol. Designates a currency value. The actual currency symbol used in the returned formatted value is the currency symbol specified in the operating system's international settings.

- + ( ) space

Literal characters. These are displayed as they appear in the format string.

\ (backslash)

Literal character prefix. The character following the backslash is displayed as is; for example, \# displays #. To display a backslash itself, precede it with another backslash; that is, \\ displays \.

"ABC"

As an alternative to \, use quotes to "escape" multiple characters. The quotes do not appear in the output.

For example, if you format the value 7034 with the format string:

#"a#c"###

the output is:

7a#c034.

The easiest way to include the doublequote character in a literal string, is to use the alternate quote characters | or {}. For example,

Format(x, {#"a#c"###})

; (semicolon)

Format section separator. Separates the positive, negative, zero, and NULL sections in fmt. If you omit the negative or zero format sections, but include the semicolons representing them, they are formatted like the positive section.

A custom format string for numeric values can have from one to four sections, separated by semicolons. In a format string with more than one section, each section applies to different values of expr. The number of sections determines the values to which each individual section applies. The following table describes how each section of a one-part or multi-part format string is used.

Number of sections

Description

One

The format applies to all numbers.

Two

The first section formats positive numbers and 0. The second section formats negative numbers.

Three

The first section formats positive numbers. The second section formats negative numbers. The third section formats 0.

Four

The first section formats positive numbers. The second section formats negative numbers. The third section formats 0. The fourth section formats NULL.

Date/time formats

Since date/time values are stored as floating point numbers, date/time values can be formatted with numeric formats. They can also be formatted with date/time formats. You can either use one of the named date/time formats shown in the following section, or create a custom date/time format using the date/time formatting codes shown in the subsequent section.

Named date/time formats

Format name

Display of the date/time value is ...

General Date

In a standard format. Converts a floating-point number to a date/time. If the number includes no fractional part, this displays only a date. If the number includes no integer part, this displays only a time.

Long Date

A Long Date as defined in the operating system's international settings.

Medium Date

dd-mmm-yy (yy/mmm/dd in Japan)

Short Date

A Short Date as defined in the operating system's international settings.

Long Time

A Long Time as defined in the operating system's international settings. Long Time always includes hours, minutes, and seconds.

Medium Time

Hours (0 - 12) and minutes using the time separator and AM/PM notation (AMPM notation in Japan)

Short Time

Hours (0 - 23) and minutes using only the time separator.

Custom date/time formatting codes

The following table describes the characters you can use in fmt to create custom formats for date/time values.

Formatting code

Meaning

: (colon)

Time separator. Separates hours, minutes, and seconds in formatted time values. The actual time separator used in the returned formatted value is the time separator specified for the given country in the operating system's international settings.

/ (slash)

Date separator. Separates day, month, and year in formatted date values. The actual date separator used in the returned formatted value is the date separator specified in the operating system's international settings.

c

Displays a date as ddddd, and a time as ttttt (see as follows). If the value includes no fractional part, only a date is displayed. If the value includes no integer part, only a time is displayed.

y

Day of the year as a number (1 - 366).

d

Day of the month as a number without a leading zero (1 - 31).

dd

Day of the month as a number with a leading zero (01 - 31).

ddd

Weekday as a three-letter abbreviation (Sun - Sat).

dddd

Weekday spelled out (Sunday - Saturday).

ddddd

Serial date number as a complete date (day, month, and year) formatted as an international Short Date string. If there is no Short Date string provided in the operating system, the date format defaults to mm/dd/yy.

dddddd

Serial date number as a complete date (day, month, and year) formatted as an international Long Date string. If there is no Long Date string provided in the operating system, the date format defaults to mmmm dd, yyyy.

w

Weekday as a number (1 - 7). Sunday is 1.

ww

Week of the year as a number (1 - 53).

m

Month of the year as a number without a leading zero (1 - 12). If the character is preceded by h in fmt, it displays the minute of the hour as a number without a leading zero (0 - 59).

mm

Month of the year as a number with a leading zero (01 - 12). If the character is preceded by h in fmt, it displays the minute of the hour as a number with a leading zero (00 - 59).

mmm

Month name as a 3-letter abbreviation (Jan - Dec).

mmmm

Month name spelled out (January - December).

q

Quarter of the year as a number (1 - 4).

yy

The last two digits of the year (00 - 99). If you specify yy in Notes® or Domino®, LotusScript® interprets 50 through 99 as the years 1950 through 1999 and 00 through 49 as the years 2000 through 2049. Note that SmartSuite® interprets yy differently.

yyyy

The full (four-digit) year (0100 - 9999).

h

Hour of the day as a number without a leading zero (0 - 23).

hh

Hour of the day as a number with a leading zero (00 - 23).

n

Minute of the hour as a number without a leading zero (0 - 59).

nn

Minute of the hour as a number with a leading zero (00 - 59).

s

Second of the minute as a number without a leading zero (0 - 59).

ss

Second of the minute as a number with a leading zero (00 - 59).

ttttt

Time serial number as a complete time (including hour, minute, and second), formatted using the time separator provided in the operating system's international settings. A leading zero is displayed if the international leading zero setting is TRUE and the time is before 10:00 AM or PM. The default time format is h:mm:ss.

AM/PM am/pm

Uses hour values from 1 to 12, displaying AM or am for hours before noon, and PM or pm for hours after noon.

A/P a/p

Uses hour values from 1 to 12, displaying A or a for hours before noon, and P or p for hours after noon.

AMPM

Uses hour values from 1 to 12. Displays the contents of the 1159 string (s1159) in WIN.INI for hours before noon, and the contents of the 2359 string (s2359) for hours after noon. AMPM is case-insensitive, but the case of the string displayed matches the string as it exists in the operating system's international settings. The default format is AM/PM.

The following table shows some custom date/time formats applied to one date and time: 6:43:04 in the evening of April 12, 1995.

fmt

Display

m/d/yy

4/12/95

d-mmm-yy

12-Apr-95

d-mmmm

12-April

mmmm-yy

April-95

y

102.00

hh:nn AM/PM

06:43 PM

h:nn:ss a/p

6:43:04 p

h:nn

18:43

h:nn:ss

18:43:04

m/d/yy h:nn

4/12/95 18:43

String formatting codes

To format a string using Format or Format$, use the formatting codes in the following table to create a custom string format. There are no named string formats.

Custom string formats can have one section, or two sections separated by a semicolon (;). If the format has one section, the format applies to all strings. If the format has two sections, then the first applies to nonempty strings, and the second applies to the value NULL and the empty string ("").

The following table describes the characters you can use in fmt to create a custom string format.

Formatting code

Meaning

@ (at sign)

Character forced display.

If the string being formatted includes a character in this position, display it. If not, display a space. @ is filled from right to left unless fmt contains an exclamation point (!).

& (ampersand)

Character optional display.

If the string being formatted includes a character in this position, display it. If not, display nothing. & is filled from right to left unless fmt contains an exclamation point (!).

< (less-than sign)

All characters in the formatted string are displayed in lowercase.

> (greater-than sign)

All characters in the formatted string are displayed in uppercase.

! (exclamation point)

Forces @ and & to fill from left to right, rather than from right to left.

Formatting dates and times in Asian languages

The Format function supports additional formatting characters for dates and times in versions of LotusScript® for Japan, China, the Taiwan region, and Korea.

Only single-byte characters are recognized as formatting characters. Double-byte characters are treated as literal characters. Some of the formatting characters for LotusScript® in China and the Taiwan region are case-sensitive (see the following paragraphs); all of the other Asian language date/time formatting characters are case-insensitive.

When a date/time formatting code used in the Format function in LotusScript® for Japan is also a date/time formatting code in WIN.INI, LotusScript® for Japan interprets the code appropriately. For example, the formatting expression "Long Date" has the same meaning in LotusScript® for Japan as in English-language LotusScript®. (The meaning is to use the WIN.INI Long Date format.)

These formats only have meanings in Asian versions of HCL products.

Date/time format codes

The first table shows the formatting codes for Japan.

Formatting code

Meaning

aaa

Weekday in abbreviated format (one double-byte character)

aaaa

Weekday in full format

e

Year in era ("0" suppressed)

ee

Year in era ("0" not suppressed)

g

Era name (single-byte one-character abbreviation)

gg

Era name (double-byte one-character abbreviation)

ggg

Full era name

This table shows the formatting codes for People's Republic of China.

Formatting code

Meaning

aaaa

Weekday in full format (three double-byte characters)

O

Month (double-byte)

o

Month (single-byte)

A

Day (double-byte)

a

Day (single-byte)

E

Short year (double-byte)

e

Short year (single-byte)

EE

Long year (double-byte)

ee

Year (single-byte)

This table shows the formatting codes for the Taiwan region.

Formatting code

Meaning

aaaa

Weekday in full format (three double-byte characters)

O

Month (double-byte)

o

Month (single-byte)

A

Day (double-byte)

a

Day (single-byte)

E

Year in era (double-byte)

e

Year in era (single-byte)

EE

Year in era with era abbreviation (double-byte)

ee

Year in era with era abbreviation (single-byte)

EEE

Year in era with era name (double-byte)

eee

Year in era with era name (single-byte)

EEEE

Christian year with Christian era name (double-byte)

eeee

Christian year with Christian era name (single-byte)

This table shows the formatting codes for Korea.

Formatting code

Meaning

aaa

Weekday in abbreviated format (one double-byte character)

aaaa

Weekday in full format (three double-byte characters)

Example