TEXT Function

Using the TEXT() function, you can change how a number or date is displayed by applying formatting.

Number formatting

The TEXT function formats numbers according to a given format. For example, if column A contains the number 23.7825 and you need to format it to three decimal places, you would use

TEXT(A;".3f")

As a result, you'd get the string "23.782".

Format:

TEXT(number;string_format)

String format:

The string format is written as follows: [flags][width][.precision]conversion

• [flags] — special symbols for formatting. For example, the "+" flag means that the numeric value must include the + symbol; the "-" flag means the result should be left-justified; and the "," flag sets the thousands separator for integers. It is not mandatory.
• [width] — A positive decimal integer that defines the minimum number of characters to be displayed. It is not mandatory.
• [.precision] — non-negative integer with a decimal point before it. Usually used to limit the number of characters. It is not mandatory.
• conversion — a symbol that indicates how the number should be formatted. You can use d for whole numbers, f for floats. This is mandatory.

As an example, let's say we have the number 12.34 in a field called "Number":

• TEXT({{Task.Number field}};"d") - result: 12
• TEXT({{Task.Number field}};"05d") - result: 00012
• TEXT({{Task.Number Field}};"04.3f") - result: 0012.340
• TEXT({{Task.Number field}};".4f") - result: 12.3400

Examples:

TEXT(A;".3f")

TEXT({{Task.Number field}};".3f")

TEXT(1234567; ",.2f") - result: 12 345,67

Formatting dates

Here's a table of formulas for date formatting.Let's look at an example of formatting the date 10/12/2016.

What to display	How	Format	Formula	Result
Months	1-12	"M"	ТЕКСТ(A;"M")	10
Months	01-12	"MM"	TEXT(A;"MM")	10
Months	jan-dec	"MMМ"	TEXT(A;"MMM")	oct
Months	January-December	"MMМM"	TEXT(A;"MMMМ")	October
Months	J-D	"MMМMM"	TEXT(A;"MMMМM")	О
Days	1-31	"D"	TEXT(A;"D")	12
Days	01-31	"DD"	TEXT(A;"DD")	12
Days	Mon-Sun	"DDD"	TEXT(A;"DDD")	Wed
Days	monday-sunday	"DDDD"	TEXT(A;"DDDD")	wednesday
Years	00-99	"YY"	TEXT(A;"YY")	16
YEARS	1900-9999	"YYYY"	TEXT(A;"YYYY")	2016

Formatting time

Here's a table of formulas for formatting time. Let's look at an example of formatting the time: 14:22:44.

What to display	How	Format	Formula	Result
Hours	0-23	"H"	TEXT(A;"H")	14
HOURS	00-23	"HH"	TEXT(A;"HH")	14
Minutes	0-59	"m"	TEXT(A;"m")	22
Minutes	00-59	"mm"	TEXT(A;"mm")	22
Seconds	0-59	"s"	TEXT(A;"s")	44
Seconds	00-59	"ss"	TEXT(A;"ss")	44
Time	14:00	"HH:00"	TEXT(A;"HH:00")	14:00
Time	14:22	"HH:mm"	TEXT(A;"HH:mm")	14:22
Time	14:22:44	"HH:mm:ss"	TEXT(A;"HH:mm:ss")	14:22:44
Time	14:22:44,11	"чч:мм:сс,00"	TEXT(A;"HH:mm:ss,00")	14:22:44,11
Time used (hours and minutes)	1:02	"[H]:mm"	TEXT(A;"[H]:mm")	1:02
Time used (hours and seconds)	62:16	"[mm]:ss"	TEXT(A;"[mm]:ss")	62:16
Time used (seconds and hundredth of a second)	3735,80	"[ss],00"	TEXT(A;"[ss],00")	3735,80

Important

• The time and date must be separated by a space.
• The time is separated by the symbol ":" and "," for milliseconds, as in 23:15:41,456.
• Dates are separated by one of the symbols ".", "/" or "-".
• If the date is not specified, it is 01.01.1900 by default.
• The date format string is quite flexible (you can enter any string, and certain symbols will be replaced with time units). Special symbols — D, M, Y, H, m, s — must be escaped to be displayed as a letter and not a time.
• You can display duration in hours/minutes/seconds (if only the date is specified, it will display the amount of time since 31.12.1899, meaning the duration in hours for 01.01.1900 is 24).
• A year specified with two digits is considered to be in the 21st century.
• If a backslash "\" is needed in the format string, it must be escaped as "\\". This is particularly important if it's at the end of the string.
• If the format ends with d/f and a date is required, d/f must be escaped. For example, TEXT("12.05.2018"; "\Hello World") results in the value: Hello World.

Go To

• Standard operators and Planfix functions
• Calculated fields
• Reports