TEXT Function: Difference between revisions
From Planfix
No edit summary |
|||
| (6 intermediate revisions by 2 users 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: | |||
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>;"05d") - result: 00012 | |||
*TEXT(<nowiki>{{Task.Number Field}}</nowiki>;"04.3f") - result: 0012.340 | |||
*TEXT(<nowiki>{{Task.Number field}}</nowiki>;".4f") - result: 12.3400 | |||
'''Examples:''' | |||
TEXT(A;".3f") | |||
TEXT(<nowiki>{{Task.Number field}}</nowiki>;".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. | |||
{| 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. | |||
TEXT(A;" | {| 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 | |||
|} | |||
TEXT( | == 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.