TEXT()

Returns the formatted text from the first argument.

Sample usage

One-argument form

TEXT(4096) returns 4,096 (Number)

TEXT("4096") returns 4096 (Text)

TEXT(32768.1024) returns 32,768.10 (Decimal)

TEXT("2019-11-01 13:34") returns 11/1/2019 1:34:00 PM (DateTime)

TEXT([price]) returns $0.41 (Price)

TEXT([percent]) ​​​​​ returns 41% (Percent)

Two-argument form

TEXT("2019-11-01", "dd/mm/yyyy") returns 01/11/2019

TEXT("2019-11-01 1:34 PM", "HH:MM")  returns 13:34

TEXT("2:23", "H") returns 2

TEXT("2:23 PM", "H") returns 14

TEXT("14:23", "H") returns 14

TEXT("14:23", "H A/P") returns 2 P

TEXT("14:23", "H AM/PM") returns 2 PM

TEXT("4/15/2020 14:23", "M") returns 4

TEXT("4/15/2020 14:23", "H:M") returns 14:23

Syntax

TEXT(value, [format])

• value - Value to format.
  • If format is omitted, the result format is determined by the value's type and its column's type details. See One-argument form notes below. 
  • If format is specified, the temporal value (Date, DateTime, or Time) to be formatted. See Two-argument form notes below. 
• format - Optional. The format to be applied to the temporal value (value). See Two-argument form notes below for recognized format characters.

Notes

This function may be used to format a value of any type as Text.

There are two forms of the function: the one-argument form and the two-argument form.

One-argument form

TEXT(value) 

The one-argument form converts a single value of any type (value) using implicit formatting according to the current locale. The locale affects such things as:

• Date format
• Decimal separator
• Thousands separator
• Time format

If the argument (value) is a column value reference (such as, [Price]), the column's Type Details settings will also be applied to the formatted result. For instance, a Decimal column's Decimal digits property can be used to limit the precision in the result; or a Date column's Use long date format can be used to produce a more verbose result.

Two-argument form

TEXT(value, format)

The two-argument form converts a single Date, DateTime, or Time value in first argument (value) according to an explicit format guide in the second argument (format).

Recognized format characters are:

• YY two-digit year (19)
• YYYY four-digit year (2019) 
• M one- or two-digit month (3, 12) 
• MM two-digit month (03, 12)
• MMM short name of the month (Mar, Dec)
• MMMM long name of the month (March, December)
• D one- or two-digit day of the month (8, 31)
• DD two-digit day of the month (08, 31) 
• DDD short name of the day of the week (Wed) 
• DDDD long name of the day of the week (Wednesday)
• H one- or two-digit hour (9, 10)
• HH two-digit hour (09, 10)
• M one- or two-digit minute (6, 58)
• MM two-digit minute (06, 58)
• S one- or two-digit second (0, 18)
• SS two-digit second (00, 18)
• AM/PM for "AM" or "PM"
• A/P for "A" or "P" 

The meaning of M is contextual: if it comes after an H or immediately before an S, it means "minutes".

If one of AM/PM or A/P is specified, hours are displayed in 12-hour format; otherwise they are displayed in 24-hour format.

Any characters not listed above are reproduced verbatim in the output text, most commonly - (hyphen), / (slash), and : (colon). 

See also

CONCATENATE()

DATE()

DATETIME()

DECIMAL()

NUMBER()

TIME()