TEXT
TEXT(value, [format])
Turns a value into text, as the grid shows it or in a format you write with Excel's codes, such as a finish date as "Fri 9 Jan" in a label. A format is for dates or for numbers, never both, and a duration takes a number format counted in working days of its task's calendar.
Arguments
| Argument | What to give it |
|---|---|
| value | Any single value: a number, a duration, a date, text, TRUE/FALSE, a status or a task. Blank gives blank. |
| format | Optional text of format codes (below). Only a number, a duration or a date takes one. Left out, the value shows as & shows it: a date as 2026-01-09, a duration as 5d in its task's working days. |
The codes:
| Codes | Show |
|---|---|
yyyy · yy | the year: 2026 · 26 |
mmmm · mmm · mm · m | the month: January · Jan · 01 · 1 |
dddd · ddd · dd · d | the day: Friday · Fri · 09 · 9 |
hh · h | the hour, 0 to 23 |
mm · m right after an hour or right before a second | the minute (hh:mm, mm:ss); anywhere else, mm is the month |
ss · s | the second |
0 | a digit, always shown: 000 shows 7 as 007 |
# | a digit shown only if needed: 0.## shows 2.5 as 2.5 and 2 as 2 |
, between digits | thousands separators: #,##0 |
. | the decimal point: 0.0 |
% | the number times 100: 0% shows 0.256 as 26% |
text in double quotes, or a character after \ | shown as written. Inside a formula a quote is doubled: "0 ""days""" |
Codes ignore case, so YYYY-MM works. Spaces and punctuation such as -, /, : and $ show as written. Numbers round half away from zero, as ROUND does. Dates are read in UTC wall clock, as every date is: TEXT([Start date], "mm") is the month MONTH gives.
Examples
Formula=TEXT([Finish date], "ddd d mmm")
The finish date as a short label: the weekday, the day and the month.
Formula="Week of " & TEXT([Start date], "d mmm yyyy")
A label for the week the task starts. Words go outside the format, joined on with &.
=TEXT([Total float], "0.0") & " days of float"
The task's float in working days of its own calendar, to one decimal. No unit is added, so the formula adds one.
Formula=TEXT(INHOURS([Duration]), "#,##0 ""h""")
The task's duration in hours, with thousands separators and an h after it.
=TEXT(INHOURS(SUM(Tasks[Duration])) * 95, "$#,##0")
As a measure: every task's hours at a rate of 95 an hour, shown as money.
Reach for something else when
| You want | Use |
|---|---|
| Several values joined into one text | &, which needs no TEXT for the default look |
| A list of values joined with a separator | TEXTJOIN |
| A number rounded but kept a number, to sort or compare | ROUND |
| A duration as a plain number of days or hours | INDAYS or INHOURS |
| The year, month or day as numbers | YEAR, MONTH, DAY |
Common questions
Why is "Week" refused in my format?
Any letter that isn't a code is an error, so a typo can't show as itself. The editor says, for example, “W isn't a format code: dates use yyyy, yy, mmmm, mmm, mm, m, dddd, ddd, dd, d, hh, h, ss (mm after hh is minutes), numbers 0, #, a comma for thousands, a point and %. Join other text on with &, e.g. "Week ” & TEXT(…)". Put words in double quotes inside the format, or join them on with &.
=TEXT([Start date], "Week of d mmm")
Why can't I give a date a number format?
A format is for one kind of value. The editor says “0.0 is a number format, and the value is a date. Format a date with yyyy, mm, dd and so on, e.g. "yyyy-mm-dd"”, and the other way round for a date format on a number or a duration. A format with both kinds of code, like "yyyy 0", is refused for mixing them.
=TEXT([Finish date], "0.0")
Can I format text or a status?
No. The editor says TEXT(value, format): only a number, a duration or a date takes a format. Leave the format out: TEXT(value).
How do I show a duration in hours?
A duration's number format counts working days of its task's calendar. For hours, count them first: TEXT(INHOURS([Duration]), "0").
What if the format comes from a column?
A format written in the formula is checked when you save. One read from a field or another column is checked on each row, and a row whose format is wrong shows an error with the same sentence.
Why is TEXT of a blank field blank, when & shows nothing?
& reads a blank as empty text, as Excel does, but TEXT keeps it blank. Join with & directly, or use COALESCE for a fallback: COALESCE(TEXT([Actual start], "d mmm"), "not started").