Format
Applies a formatting rule to a string or numeric value. For example, you can change the format of an ISO 8601 timestamp to show the date in the format that you prefer.
Format(1, 2)
| Ordinal | Type | Description |
|---|---|---|
1 | String or number | Required. The string or number to apply a formatting rule to. |
2 | String | Required. A format code to apply to the string. |
Use these codes to format numeric values or strings that contain numeric values. The values in the Example column are based on a sample numeric value of 4213.65.
| Formatting Code | Description | Example |
|---|---|---|
C | Currency value with group and decimal separators | $4,213.65 |
D | Decimal value | 4213.65 |
E | Exponential notation value | 4.213650E+003 |
F | Fixed-point value | 4213.65 |
G | The more compact of either fixed-point or exponential notation | 4213.65 |
N | Number with group and decimal separators | 4,213.65 |
P | Percentage with group and decimal separators | 421,365.00% |
Include a number after any formatting code to modify the number of decimal places in the output, rounding up or down as needed. For example, if the input value is 4213.65 and you specify the C0 formatting code, the output value is $4,214. With the same starting value and a formatting code of C4, the output value becomes $4,213.6500.
To apply a formatting rule to a date, specify the format that you want the function to output. You can use a predefined date format or specify a custom date format.
This table lists the predefined date formats for date values. The values in the Example column are based on the date Monday, August 5, 2024 at 1:41:23 PM in the UTC-06:00 time zone.
| Formatting Code | Description | Example |
|---|---|---|
d | Short-form date | 8/5/24 |
M | Month and day | August 5 |
f | Long-form date with 12-hour time | Monday, August 5, 2024 1:41 PM |
g | Short-form date with 12-hour time | 8/5/2024 1:41 PM |
O | ISO 8601 timestamp | 2024-08-05T13:41:23.0000000 |
r | RFC 1123 timestamp | Mon, 05 Aug 2024 13:41:23 GMT |
s | Sortable timestamp | 2024-08-05T13:41:23 |
t | 12-hour time | 1:41 PM |
T | 12-hour time in seconds | 1:41:23 PM |
u | Universal sortable timestamp | 2024-08-05 13:41:23Z |
U | Long-form date with 12-hour UTC time in seconds | Monday, August 5, 2024 7:41:23 PM |
y | Month and year | August 2024 |
Combine the format codes listed in this table to output a date string that uses a custom format. The values in the Example column are based on the date Monday, August 5, 2024 at 1:41:23 PM in the UTC-06:00 time zone.
| Formatting Code | Description | Example |
|---|---|---|
d | Day expressed as a numeral without a leading zero | 5 |
dd | Day expressed as a numeral | 05 |
ddd | Abbreviated day name | Mon |
dddd | Day name | Monday |
h | Hours on a 12-hour clock without a leading zero | 1 |
hh | Hours on a 12-hour clock | 01 |
HH | Hours on a 24-hour clock | 13 |
mm | Minutes | 41 |
M | Month expressed as a numeral without a leading zero | 8 |
MM | Month expressed as a numeral | 08 |
MMM | Abbreviated month name | Aug |
MMMM | Month name | August |
ss | Seconds | 23 |
tt | 12-hour time period (AM or PM) | PM |
yy | Last two digits of the year | 24 |
yyy | Year | 2024 |
z | Time zone offset in hours without a leading zero | -6 |
zz | Time zone offset in hours | -06 |
zzz | Time zone offset in hours and minutes | -06:00 |
Some format codes for custom dates are the same as the codes for predefined date formats. When you pass a single formatting code as a parameter for the Format function, the function outputs a date string in the specified format. If you pass more than one formatting code as a parameter, the function outputs a string with the date-formatting options you specified. For example, using the same example timestamp that’s used in the table, the function Format(date, "d") outputs 8/5/24. However, the function Format(date, "d MMMM") outputs 5 August.
You can use this function to format strings in several different ways. These examples show some of the ways that you can use this function.
Use the C formatting code to express a string or number as a currency value. The function in this example specifies that the output includes two decimal places (C2).
The function outputs the string expressed as a dollar amount rounded to two decimal places.
This example shows how to format a date by using a predefined format and a custom format.
This example uses the O formatting code to format the incoming date and time value as an ISO timestamp. Next, it uses a custom format for the incoming date and time. The custom format includes the full name of the day (dddd), the full name of the month (MMMM), the date without a leading zero (d), and the year (yyy). It also shows the time in 24-hour format, including hours, minutes, seconds, and UTC offset (HH:mm:ss (zzz)).
The function outputs the date and time using the specified formats.