Syntax
Text( NumberOrDateTime, DateTimeFormatEnum [, ResultLanguageTag ] )
- NumberOrDateTime – Required. The number or the date/time value to format.
- DateTimeFormat – Required. A member of the DateTimeFormat enumeration.
- ResultLanguageTag – Optional. The language tag to use for the result text. By default, the language of the current user is used.
Text( NumberOrDateTime, CustomFormat [, ResultLanguageTag ] )
- Number – Required. The number or the date/time value to format.
- CustomFormat – Required. One or more placeholders enclosed in double quotation marks.
- ResultLanguageTag – Optional. The language tag to use for the result text. By default, the language of the current user is used.
Text( AnyValue )
- AnyValue – Required. Value to convert to a text representation. A default format is used.
Converts any value and formats a number or date/time value to a string of text.
Examples
“FY” & Text(DateAdd(Dollar_Gant_.Dollar_Gant_Current_Date, Dollar_Gant_.Dollar_Gant_Report_FY_Start -2 , Years),”yy”)
Returns: FY20
Predefined date/time formats
For these examples, date and time used is Tuesday, April 7, 2020 8:26:59.180 PM, in the time zone UTC-7 hours.
| DateTimeFormat enum | Description | Examples (using en-US) |
|---|---|---|
| LongDate | Four-digit year, month name, day of the month, and day of the week. The names of the month and day of the week aren’t abbreviated. | “Tuesday, April 7, 2020” |
| LongDateTime | Four-digit year, month name, day of the month, and day of the week, plus hour (12-hour clock), minutes, seconds, and AM/PM designation. The names of the month and day of the week aren’t abbreviated. | “Tuesday, April 7, 2020 8:26:59 PM” |
| LongDateTime24 | Four-digit year, month, day of the month, and day of the week, plus hour (24-hour clock), minutes, and seconds. The names of the month and day of the week aren’t abbreviated. | “Tuesday, April 7, 2020 20:26:59” |
| LongTime | Hour (12-hour clock), minutes, seconds, and AM/PM designation. | “8:26:59 PM” |
| LongTime24 | Hour (24-hour clock), minutes, seconds. | “20:26:59” |
| ShortDate | Four-digit year with numerical month and day of the month. | “4/7/2020” |
| ShortDateTime | Four-digit year with numerical month and day of the month, plus hour (12-hour clock), minutes, and AM/PM designation. | “4/7/2020 8:26 PM” |
| ShortDateTime24 | Four-digit year with numerical month and day of the month, plus hour (24-hour clock) and minutes. | “4/7/2020 20:26” |
| ShortTime | Hour (12-hour clock), minutes, and AM/PM designation. | “8:26 PM” |
| ShortTime24 | Hour (24-hour clock) and minutes. | “20:26” |
| UTC | The date/time value is converted to UTC based on the current user’s time zone and formatted according to the ISO 8601 standard. | “2020-04-08T03:26:59.180Z” |
Number placeholders
| Placeholder | Description |
|---|---|
| 0 (zero) | Displays insignificant zeros if a number has fewer digits than there are zeros in the format. For example, use the format #.00 if you want to display 8.9 as 8.90. |
| # | Follows the same rules as the 0 (zero). However, Text doesn’t return extra zeros when the number has fewer digits on either side of the decimal than there are # symbols in the format. For example, 8.9 is displayed if the custom format is #.## and the number to format is 8.9. |
| . (period) | Displays the decimal point in a number. Depends on the language of the custom format; see global apps for more details. |
| , (comma) | Displays the grouping separator in a number, often used for thousands. Text separates groups by commas if the format contains a comma that’s enclosed by number signs (#) or by zeros. Depends on the language of the custom format; see global apps for more details. |
If a number has more digits to the right of the decimal point than there are placeholders in the format, the number rounds to as many decimal places as there are placeholders. If there are more digits to the left of the decimal point than there are placeholders, the extra digits are displayed. If the format contains only number signs (#) to the left of the decimal point, numbers less than 1 start with a decimal point (for example, .47).
Date and time placeholders
| Placeholder | Description |
|---|---|
| m | Displays the month as a number without a leading zero. |
| mm | Displays the month as a number with a leading zero when appropriate. |
| mmm | Displays the month as an abbreviation (Jan to Dec). |
| mmmm | Displays the month as a full name (January to December). |
| d | Displays the day as a number without a leading zero. |
| dd | Displays the day as a number with a leading zero when appropriate. |
| ddd | Displays the day as an abbreviation (Sun to Sat). |
| dddd | Displays the day as a full name (Sunday to Saturday). |
| yy | Displays the year as a two-digit number. |
| yyyy | Displays the year as a four-digit number. |
| h | Displays the hour as a number without a leading zero. |
| hh | Displays the hour as a number with a leading zero when appropriate. If the format contains AM or PM, the hour is shown based on the 12-hour clock. Otherwise, the hour is shown based on the 24-hour clock. |
| m | Displays the minute as a number without a leading zero. This placeholder must appear immediately after the h or hh code or immediately before the ss code; otherwise, Text returns the month instead of minutes. |
| mm | Displays the minute as a number with a leading zero when appropriate. This placeholder must appear immediately after the h or hh placeholder or immediately before the ss placeholder. Otherwise, Text returns the month instead of minutes. |
| s | Displays the second as a number without a leading zero. |
| ss | Displays the second as a number with a leading zero when appropriate. |
| f | Displays the fractions of seconds. |
| AM/PM, a/p | Displays the hour based on a 12-hour clock. Text returns “AM” or “a” for times from midnight until noon and “PM” or “p” for times from noon until midnight |
Literal placeholders
You can include any of these characters in your format string. They will appear in the result of Text as is. Additional characters are reserved for future placeholders, so you shouldn’t use them.
| Character | Description |
|---|---|
| Any currency symbol | Dollar sign, cents sign, euro sign, etc. |
| + | Plus sign |
| ( | Left parenthesis |
| : | Colon |
| ^ | Circumflex accent (caret) |
| ‘ | Apostrophe |
| { | Left curly bracket |
| < | Less-than sign |
| = | Equal sign |
| – | Minus sign |
| / | Slash mark |
| ) | Right parenthesis |
| & | Ampersand |
| ~ | Tilde |
| } | Right curly bracket |
| > | Greater-than sign |
| Space character |
Examples
Unless otherwise specified, the user running these formulas is located in the United States and has selected English as their language. The Language function is returning “en-US”.
Number
| Formula | Description | Result |
|---|---|---|
| Text( 1234.59, “####.#” ) | Formats the number with one decimal place. | “1234.6” |
| Text( 8.9, “#.000” ) | Pads the decimal portion of the number with trailing zeros, if needed. | “8.900” |
| Text( 0.631, “0.#” ) | Pads the whole portion of the number with leading zeros, if needed. | “0.6” |
| Text( 12, “#.0#” ) Text( 1234.568, “#.0#” ) | Pads the decimal portion of the number with zeros for one decimal place, and includes a second decimal place if supplied. | “12.0” “1234.57” |
| Text( 12000, “$ #,###” ) Text( 1200000, “$ #,###” ) | Places a thousands separator every three digits, and includes a currency symbol. | “$ 12,000” “$ 1,200,000” |
Date/Time
- At 2:37:47 PM on Monday, November 23, 2015
- United States Pacific Time Zone (UTC-8)
| Formula | Description | Result |
|---|---|---|
| Text( Now(), DateTimeFormat.LongDate ) | Formats as a long date string, in the language and locale of the current user. | “Monday, November 23, 2015” |
| Text( Now(), DateTimeFormat.LongDateTime ) | Formats as a long date and time string, in the language and locale of the current user, using a 12-hour clock. | “Monday, November 23, 2015 2:37:47 PM” |
| Text( Now(), DateTimeFormat.LongTime24 ) | Formats as a long time string, using a 24-hour clock. | “14:37:47” |
| Text( Now(), DateTimeFormat.ShortDate ) | Formats as a short date string, in the language and locale of the current user. | “11/23/2015” |
| Text( Now(), “d-mmm-yy” ) | Formats using placeholder characters:d for a single-digit or double-digit day of the month– as a literal character copied to the resultmmm for a three-letter abbreviation of the month– as another literal character copied to the resultyy for a two-digit abbreviation of the year | “23-Nov-15” |
| Text(1448318857*1000, “mmm. dd, yyyy (hh:mm:ss AM/PM)”) | Shows a Unix date-time value in human-readable format if you multiply the source value by 1,000. | “Nov. 23, 2015 (02:47:37 PM)” |
Global apps
| Formula | Description | Result |
|---|---|---|
| Text(1234567.89, “[$-fr-FR]# ###,## €”, “fr-FR”) | Shows a space as a grouping separator, the comma as a decimal separator, and € as the currency symbol. | “1 234 567,89 €” |
| Text(1234567,89; “[$-fr-FR]# ###,## €”) | If the source data follows the French custom of using a comma as the decimal separator, you must change your locale to French and separate the arguments with a semi-colon instead of a comma to get the same result as above. | “1 234 567,89 €” |
| Text( Date(2016,1,31), “dddd mmmm d” ) | Returns the weekday, month, and day of the month in the language of the current user. Because none of the placeholders are language dependent, there is no need for a format text language tag. | “Sunday January 31” |
| Text( Date(2016,1,31), “dddd mmmm d”, “es-ES” ) | Returns the weekday, month, and day of the month in the “es-ES” language. | “domingo enero 31” |
Converting values to text
| Formula | Description | Result |
|---|---|---|
| Text( 1234567.89 ) | Converts a number to a string. There are no thousands separators or control over the number of digits before or after the decimal separator; for more control, supply number placeholders as the second argument. | “1234567.89” |
| Text( DateTimeValue( “01/04/2003” ) ) | Converts a date/time value to a string of text. To control the conversion, provide either a member of the DateTimeFormat enumeration or a custom-format string. | “1/4/2003 12:00 AM” |
| Text( true ) | Converts a Boolean value to a string. | “true” |
| Text( GUID() ) | Converts a generated GUID value to a string. | “f8b10550-0f12-4f08-9aa3-bb10958bc3ff” |
| Left( Text( GUID() ), 4 ) | Returns the first four characters of a generated GUID. | “2d9c” |