The FORMAT function converts a value to text according to a specific format.
FORMAT(
value,
format_string
)
- value: Value or expression that, returning a scalar, you want to convert to text.
- format_string: Text string with the template of the format to apply.
The FORMAT function returns a text string.
If value is Blank, the function returns an empty text string. If format_string is Blank, the function returns the value in the format "General Number" (if it is a number) or in the format "General Date" (if it is a date or time).
The available formats for numeric values are:
- General Number: Shows a value without thousands separator.
- Currency: Displays a value in currency format based on locale settings. Includes thousands separator and two decimal places.
- Fixed: Shows a value with at least one digit to the left of the decimal point and two digits to its right.
- Standard: Shows a value with a thousands separator, at least one digit to the left of the decimal point and two digits to its right.
- Percent: Shows a value multiplied by 100, with two digits to the right of the decimal point and the % sign to the right of the result.
- Scientific: Displays a value in scientific format (in the form NE+n, where N is a number with two decimal places that multiplies to 10 raised to n).
- Yes/No: Shows "No" if the value is 0 and "Yes" in any other case.
- True/False: Shows "False" if the value is 0 and "True" in any other case.
- On/Off: Shows "Off " if the value is 0 and "On" in any other case.
Detailed information on custom formats for numeric values can be found in the official documentation.
The available formats for dates and times are:
- General Date: Displays a date or time. The exact format will depend on the local culture value.
- Long Date, Medium Date or Short Date: Displays a date based on the long, medium, or short format of the local culture value.
- Long Time: Displays a time in the long format of the local culture value. Typically includes the hour, minute, and second.
- Medium Time: Shows a time in 12 hour format.
- Short Time: Shows a time in 24 hour format.
In this example we want to know the time elapsed between the purchase and today, for which the NOW function is used. As the result of the subtraction would be in datetime format, the FORMAT function is used to give it a number format with two decimal places:
