Format
Convert Numeric, Date, Time, and Timestamp values to text using formatting options, user-specific locale, and timezone settings.
Variants
- Format(column)
- Format(Date date, Text format)
- Format(Date date, Text format, Text culture)
- Format(Numeric number, Text format)
- Format(Numeric number, Text format, Text unit)
- Format(Numeric number, Text format, Text unit, Text culture)
- Format(Time time, Text format)
- Format(Time time, Text format, Text culture)
- Format(Timestamp t, Text format)
- Format(Timestamp t, Text format, Text culture)
Format(column)
Returns the text representing the column value using formatting options from the column properties, user-specific locale, and timezone settings.
Parameters
- column
- The column whose value you need to convert to text. Allowed column types are: Numeric, Date, Time, Timestamp.
Returns
Text
Examples
Format([Date Column])converts the date value stored in the "Date Column" to a text value.Format([Numeric Column])converts the numeric value stored in the "Numeric Column" to a text value.Format([Time Column])converts the time value stored in the "Time Column" to a text value.Format([Timestamp Column])converts the timestamp value stored in the "Timestamp Column" to a text value.
Format(Date date, Text format)
Returns the text representation of the date using the specified format string.
Parameters
- date
- The date value you need to convert to text.
- format
- The date format string. You can specify one of the following format strings to convey the meaning:
| Format | Meaning |
|---|---|
| "d" | Short Date |
| "D" | Long Date |
| "M" | Month Day |
| "Y" | Year Month |
This function supports several additional standard and custom date format strings.
Returns
Text
Examples
Format(#2009-06-15#, "D")returns the text value "Monday, June 15, 2009" for the "English (United States)" user locale.Format(#2012-02-14#, "MM/yy")takes the date value 2012-02-14 and returns the text value "02/12".
Format(Date date, Text format, Text culture)
Returns the text representation of the date using the specified format string and culture.
Parameters
- date
- The date value you need to convert to text.
- format
- The date format string.
- culture
- The culture name from the list of valid culture identifiers.
Returns
Text
Examples
Format(#2009-06-15#, "D", "en-US")returns the text value "Monday, June 15, 2009".Format(#2009-06-15#, "D", "es-ES")returns the text value "lunes, 15 de junio de 2009".Format(#2009-06-15#, "D", "el-GR")returns the text value "Δευτέρα, 15 Ιουνίου 2009".
Format(Numeric number, Text format)
Returns the text representation of the number using the specified format string.
Parameters
- number
- The numeric value you need to convert to text.
- format
- The numeric format string. You can specify one of the following format strings to convey the meaning:
| Format | Meaning |
|---|---|
| "$#" | Unit to the left, no digit grouping, hide zero values, no fractional part |
| "#,#" | Digit grouping, hide zero values, no fractional part |
| "#,0" | Digit grouping, display zero values, no fractional part |
| "0" | No digit grouping, display zero value, no fractional part. This is the default format for Numeric columns |
| "0%" | Unit to the right, no digit grouping, display zero values, no fractional part, multiply value by 100. This is the default format for Numeric - Percent columns. |
| "$#,#.####" | Unit to the left, digit grouping, hide zero values, 4 fractional digits, trim trailing zeroes |
| "-$#,0.00" | Unit between sign and number, digit grouping, display zero values, 2 fractional digits, display trailing zeros. This is the default format for Numeric - Currency columns. |
This function supports a number of additional standard and custom numeric format strings.
Returns
Text
Examples
Format(1500.63,"$#")returns the text value "$15001".Format(0.3,"$#")returns a blank text value with the unit sign "$".Format(1500.63,"#,#")returns the text value "1,501".Format(0.3,"#,#")returns a blank text value.Format(1500.63,"#,0")returns the text value "1,501".Format(0.3,"#,0")returns the text value "0".Format(1500.63,"0")returns the text value "1501".Format(0.3,"0")returns the text value "0".Format(1.63,"0%")returns the text value "163%".Format(0.0003,"0%")returns the text value "0%".Format(2.0039900, "$#,#.####")returns the text value "$2.004".Format(0.000002, "$#,#.####")returns a blank text value.Format(-5.204, "-$#,0.00")returns the text value "-$5.20".Format(0.003, "-$#,0.00")returns the text value "$0.00".
Format(Numeric number, Text format, Text unit)
Returns the text representation of the number using the specified format string and unit string.
Parameters
- number
- The numeric value you need to convert to text.
- format
- The numeric format string.
- unit
- The unit string to use instead of the format string "$" or "%".
Returns
Text
Examples
Format(1500.63,"$#", "£")returns the text value "£15001".Format(0.3,"$#","£")returns a blank text value with the unit sign "£".Format(1500.63,"#,#","£")returns the text value "1,501£".Format(0.3,"#,#","£")returns a blank text value with the unit sign "£".Format(1500.63,"#,0","£")returns the text value "1,501£".Format(0.3,"#,0","£")returns the text value "0£".Format(1500.63,"0","£")returns the text value "1501£".Format(0.3,"0","£")returns the text value "0£".Format(1.63,"0%","£")returns the text value "163£".Format(0.0003,"0%","£")returns the text value "0£".Format(2.0039900, "$#,#.####","£")returns the text value "£2.004".Format(0.000002, "$#,#.####","£")returns a blank text value with the unit sign "£".Format(-5.204, "-$#,0.00","£")returns the text value "-£5.20".Format(0.003, "-$#,0.00","£")returns the text value "£0.00".
Format(Numeric number, Text format, Text unit, Text culture)
Returns the text representation of the number using the specified format string, unit string, and culture name.
Parameters
- number
- The numeric value you need to convert to text.
- format
- The numeric format string.
- unit
- The unit string to use instead of the format string "$" or "%".
- culture
- The culture name from the list of valid culture identifiers.
Returns
Text
Examples
Format(123456.789, "C", "$", "en-US")returns the text value "$123,456.79".Format(123456.789, "C", "€", "fr-FR")returns the text value "€123,456.79".Format(123456.789, "C", "¥", "ja-JP")returns the text value "¥123,456.79".
Format(Time time, Text format)
Returns the text representation of the time using the specified format string.
Parameters
- time
- The time value you need to convert to text.
- format
- The time format string. You can specify one of the following format strings to return the meaning:
| Format | Meaning |
|---|---|
| "t" | Short Time |
| "T" | Long Time |
This function supports a number of additional standard and custom date format strings.
Returns
Text
Examples
Format(#10:15:11#, "t")returns the text value "10:15 AM".Format(#10:15:11#, "T")returns the text value "10:15:11 AM".
Format(Time time, Text format, Text culture)
Returns the text representation of the time using the specified format string and culture name.
Parameters
- time
- The time value you need to convert to text.
- format
- The time format string.
- culture
- The culture name from the list of valid culture identifiers.
Returns
Text
Examples
Format(#13:45:30#, "t", "en-US")returns the text value "1:45 PM".Format(#13:45:30#, "t", "hr-HR")returns the text value "13:45".Format(#13:45:30#, "t", "ar-EG")returns the text value "01:45م".
Format(Timestamp timestamp, Text format)
Returns the text representation of the timestamp using the specified format string.
Parameters
- time
- The timestamp value you need to convert to text.
- format
- The time format string. You can specify one of the following format strings to return the meaning:
| Format | Meaning |
|---|---|
| "g" | Short Date Short Time |
| "G" | Short Date Long Time |
| "f" | Date Short Time |
| "F" | Long Date Long Time |
This function supports a number of additional standard and custom date format strings.
Returns
Text
Examples
Format(#2012-11-05 09:18:06#, "g")returns the text value "11/5/2012 9:18 AM".Format(#2012-11-05 09:18:06#, "G")returns the text value "11/5/2012 9:18:06 AM".Format(#2012-11-05 09:18:06#, "f")returns the text value "Monday, November 05, 2012 9:18 AM".Format(#2012-11-05 09:18:06#, "F")returns the text value "Monday, November 05, 2012 9:18:06 AM".
Format(Timestamp timestamp, Text format, Text culture)
Returns the text representation of the timestamp using the specified format string and culture name.
Parameters
- time
- The timestamp value you need to convert to text.
- format
- The time format string.
- culture
- The culture name from the list of valid culture identifiers.
Returns
Text
Examples
Format(#2009-06-15 13:45:30#, "G", "en-US")returns the text value "6/15/2009 4:45:30 PM".Format(#2009-06-15 13:45:30#, "G", "da-DK")returns the text value "15-06-2009 16:45:30".Format(#2009-06-15 13:45:30#, "G", "zh-CN")returns the text value "2009/6/15 16:45:30".