TEXT Function: Difference between revisions

From Planfix
Jump to: navigation, search
No edit summary
 
(3 intermediate revisions by the same user not shown)
Line 1: Line 1:
{{#seo:
|title=TEXT Function
|titlemode=append
|keywords=planfix, Function, TEXT, reports
|description=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
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


Line 5: Line 13:
As a result, you'd get the string "23.782".
As a result, you'd get the string "23.782".


Format:
''Format:''


TEXT(number;string_format)
'''TEXT(number;string_format)'''


   
   
''String format:''
'''String format:'''


The string format is written as follows: [flags][width][.precision]conversion
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.
* [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.
* [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.
* [.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.
* 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 field."
 


As an example, let's say we have the number 12.34 in a field called "Number":
*TEXT(<nowiki>{{Task.Number field}}</nowiki>;"d") - result: 12
*TEXT(<nowiki>{{Task.Number field}}</nowiki>;"d") - result: 12
*TEXT(<nowiki>{{Task.Number field}}</nowiki>;"05d") - result: 00012
*TEXT(<nowiki>{{Task.Number field}}</nowiki>;"05d") - result: 00012
Line 28: Line 33:
*TEXT(<nowiki>{{Task.Number field}}</nowiki>;".4f") - result: 12.3400
*TEXT(<nowiki>{{Task.Number field}}</nowiki>;".4f") - result: 12.3400


'''Examples:'''
 
''Examples:''


TEXT(A;".3f")
TEXT(A;".3f")
Line 38: Line 41:
TEXT(1234567; ",.2f") - result: 12 345,67
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.
{| class="wikitable" style="text-align: center;"
|'''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.
{| class="wikitable" style="text-align: center;"
|'''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.




Latest revision as of 14:58, 29 March 2024

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

Retrieved from "http://planfix.com/help/index.php?title=TEXT_Function&oldid=20204"