Sample Format Number Expressions

Some sample format expressions for numbers are shown below. (These examples all assume the Country is set to United States in the International section of the Control Panel.) The first column contains the format strings. The other columns contain the output the results if the formatted data has the value given in the column headings

Format (fmt)

Positive 3

Negative 3

Decimal .3

Null

Null string

3

-3

0.3

 

0

3

-3

1

 

0.00

3.00

-3.00

0.30

 

#,##0

3

-3

1

 

#,##0.00;;;Nil

3.00

-3.00

0.30

Nil

$#,##0;($#,##0)

$3

($3)

$1

 

$#,##0.00;($#,##0.00)$3.00

($3.00)

$0.30

 

 

0%

300%

-300%

30%

 

0.00%

300.00%

-300.00%

30.00%

 

0.00E+00

3.00E+00

-3.00E+00

3.00E-01

 

0.00E-00

3.00E00

-3.00E00

3.00E-01

 

Numbers can also be used to represent date and time information. You can format date and time serial numbers using date and time formats or number formats because date/time serial numbers are stored as floating-point values.

To format dates and times, you can use either the commonly used format that have been predefined or create user-defined time formats using standard meaning of each:

The following table shows the predefined data format names you can use and the meaning of each.

Format

Name

Description

General

Display a date and/or time. for real numbers, display a date and time.(e.g. 4/3/93 03:34 PM); If there is no fractional part, display only a date (e.g. 4/3/93); if there is no integer part, display time only (e.g. 03:34 PM).

Long Date

Display a Long Date, as defined in the International section of the Control Panel.

Medium

Display a date in the same form as the Short Date, as defined in the international section of the Control Panel, except spell out the month abbreviation.

Short Date

Display a Short Date, as defined in the International section of the Control Panel.

Long Time

Display a Long Time, as defined in the International section of the Control panel. Long Time includes hours, minutes, seconds.

Medium Time

Display time in 12-hour format using hours and minutes and the Time AM/PM designator.

Short Time

Display a time using the 24-hour format (e.g. 17:45)

This table shows the characters you can use to create user-defined date/time formats.

Character

Meaning

c

Display the date as dddd and display the time as ttttt. in the order.

d

Display the day as a number without a leading zero (1-31).

dd

Display the day as a number with a leading zero (01-31).

ddd

Display the day as an abbreviation (Sun-Sat).

ddddd

Display a date serial number as a complete date (including day , month, and year).

 

Character

Meaning

w

Display the day of the week as a number (1- 7 ).

ww

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

m

Display the month as a number without a leading zero (1-12). If m immediately follows h or hh, the minute rather than the month is displayed.

mm

Display the month as a number with a leading zero (01-12). If mm immediately follows h or hh, the minute rather than the month is displayed.

mmm

Display the month as an abbreviation (Jan-Dec).

mmmm

Display the month as a full month name (January-December).

q

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

y

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

yy

Display the day of the year as a two-digit number (00-99).

yyyy

Display the day of the year as a four-digit number (100-9999).

h

Display the hour as a number without leading zeros (0-23).

hh

Display the hour as a number with leading zeros (00-23).

n

Display the minute as a number without leading zeros (0-59).

nn

Display the minute as a number with leading zeros (00-59).

s

Display the second as a number without leading zeros (0-59).

ss

Display the second as a number with leading zeros (00-59).

tttt

Display a time serial number as a complete time (including hour, minute, and second) formatted using the time separator defined by the Time Format in the International section of the Control Panel. A leading zero is displayed if the Leading Zero option is selected and the time is before 10:00 A.M. or P.M. The default time format is h:mm:ss.

AM/PM

Use the 12-hour clock and display an uppercase AM/PM

am/pm

Use the 12-hour clock display a lowercase am/pm

 

Character

Meaning

A/P

Use the 12-hour clock display a uppercase A/P.

a/p

Use the 12-hour clock display a lowercase a/p.

AMPM

Use the 12-hour clock and display the contents of the 11:59 string (s1159) in the WIN.INI file with any hour before noon; display the contents of the 2359 string (s2359) with any hour between noon and 11:59 PM. AMPM can be either uppercase or lowercase, but the case of the string displayed matches the string as it exists in the WIN.INI file. The default format is AM/PM.

 

The Following are examples of user-defined date and time formats:

Format

Display

m/d/yy

2/26/65

d-mmmm-yy

26-February-65

d-mmmm

26 February

mmmm-yy

February 65

hh:nn AM/PM

06:45 PM

h:nn:ss a/p

6:45:15 p

h:nn:ss

18:45:15

m/d/yy/h:nn

2/26/65 18:45

 

Strings can also be formatted with Format[$]. A format expression for strings can have one section or two sections separated by a semicolon.

If you use

The result is

One section only

The format applies to all string data.

Two sections

The first section applies to string data, the second to Null values and zero-length strings.

 

The following characters can be used to create a format expression for strings:

Character

Meaning

@

Character placeholder. Displays a character or a space. Placeholders are filled from right to left unless there is an ! character in the format string.

&

Character placeholder. Display a character or nothing.

<

Force lowercase.

>

Force uppercase.

!

Force placeholders to fill from left to right instead of right to left.

 

Related Topics: Str, Str$ Function.

More:

Example: