01 / 11The Notices Said "From 46419"

Text FunctionsExcelDate Formats

Excel TEXT Function: Join Text With Dates and Numbers

Nine Rent-Increase Notices Said the New Rent Started From 46419, Because & Joins the Number Under a Date, Not the Date on Screen

31 Oct 20268 min read

UsesCONCAT and &DOLLARROUNDCOUNT, COUNTA

Hollins Lettings raised fourteen rents by 3.5% from 1 February. One formula wrote every notice. Nine of them gave the start date as 46419, the notices had to be served again, and the increases began two months late.

Rent review, 1 February 2027Figure
Notices sent14
Dates printed as 464199
Rents quoted to three decimals7
Monthly increase on the nine flats£373.30
Months lost2
Rent lost£746.60

Scope. TEXT, ROUND, DOLLAR and the & operator work in every version of Excel. TEXTJOIN in section 7 needs Excel 2019 or Microsoft 365.

Throughout, the review sheet is in A1:G15: flat in A, current rent in B, new rent in C and start date in D. The finished notice goes in column H.

01The Notices Said "From 46419"

The tenancy agreements ask for two months' written notice stating the new rent and the date it starts. The lettings assistant built the sentence once and filled it down fourteen rows.

="New rent for "&A2&": £"&C2&" a month from "&D2

On Alder Ct 4 it produced New rent for Alder Ct 4: £1319.625 a month from 46419. The notices went out on 25 November, and nobody read one before it left.

Fourteen Notices, Nine Dates Printed as 46419

Hollins Lettings' rent review sheet, in A1:G15. Column C is the current rent times 1.035, formatted to two decimals, and column D the start date, formatted dd/mm/yyyy. Column E says whether D holds a real date or text. Columns F and G are what the notice formula's & wrote into each sentence: the rent with every decimal it had, and the date as a serial wherever D held a real date.

ABCDEFG
1
Flat
Current rent
New rent
Starts
Date stored as
Rent in notice
Date in notice
2
Alder Ct 1
850
879.75
01/02/2027
Text
£879.75
01/02/2027
3
Alder Ct 2
980
1014.3
01/02/2027
Date
£1014.3
46419
4
Alder Ct 4
1275
1319.625
01/02/2027
Date
£1319.625
46419
5
Brook Hse 3
1150
1190.25
01/02/2027
Text
£1190.25
01/02/2027
6
Brook Hse 7
1090
1128.15
01/02/2027
Date
£1128.15
46419
7
Cedar Mews 2
1425
1474.875
01/02/2027
Date
£1474.875
46419
8
Cedar Mews 5
925
957.375
01/02/2027
Text
£957.375
01/02/2027
9
Dene Rd 14
1210
1252.35
01/02/2027
Date
£1252.35
46419
10
Dene Rd 16
1365
1412.775
01/02/2027
Date
£1412.775
46419
11
Elm Yard 1
795
822.825
01/02/2027
Text
£822.825
01/02/2027
12
Elm Yard 6
1040
1076.4
01/02/2027
Date
£1076.4
46419
13
Fenn St 9
1185
1226.475
01/02/2027
Date
£1226.475
46419
14
Fenn St 11
1330
1376.55
01/02/2027
Text
£1376.55
01/02/2027
15
Garth Ln 2
1095
1133.325
01/02/2027
Date
£1133.325
46419

fxCells with formulas are highlighted in green

Hover over formula cells to see the formula and highlight referenced cells

Three tenants rang to ask what 46419 meant. By the time the office reopened on 6 January, a fresh two months' notice could only take effect from 1 April.

02& Joins the Value, Not the Format

A cell holds a value and shows it through a format. The & operator takes the value and throws the format away. A date is a serial number underneath, so it joins as a serial number.

Cell showsValue underneathJoined with &
01/02/20274641946419
£1,319.631319.6251319.625
£1,014.301014.31014.3
3.5%0.0350.035

The same is true of CONCAT, CONCATENATE and TEXTJOIN. None of them can see the format, so none of them can copy it. The pound sign, the comma, the trailing zero and the slashes all live in the format.

Scenario: Put a date in D2 and format it as a date. In another cell enter ="from "&D2 and read the serial number it returns. Then format D2 as a percentage and notice that the joined text does not change at all.

Try it in the grid

03The Wrong Dates Were the Real Ones

Five notices read correctly, and that was the clue. Their dates were text, imported from the previous agent's export, so & had nothing to convert. The other nine had been typed in by hand as real dates.

Both kinds display 01/02/2027 in the sheet, and only one kind is a date. Count them:

=COUNT(D2:D15)

That returns 9, because COUNT counts numbers and a real date is a number. Fourteen rows and nine numbers means five dates are text.

The five text dates are the ones to fix, even though their notices were right. Text cannot be sorted by date or added to, and the next formula that does sums with it will fail. Convert them with =DATEVALUE(D2) and paste the values back.

Scenario: Enter =COUNT(D2:D15) beside the review sheet and check it returns 9. Then enter =ISTEXT(D2) in a spare column, fill it down, and find the five flats taken over from the old agent.

Try it in the grid

04TEXT Writes the Value Through a Format

TEXT takes a value and a format code and returns the text the cell would have shown. It is the one tool that carries a format into a sentence.

=TEXT(D2,"d mmmm yyyy")      → 1 February 2027
=TEXT(C2,"£#,##0.00")        → £1,319.63
=TEXT(0.035,"0.0%")          → 3.5%

The codes are the ones in Format Cells, Custom. d is the day without a leading zero, mmmm the month in full and yyyy the year. In a number code, 0 forces a digit and #,##0 adds the thousands comma.

The notice becomes one sentence that reads the same for every flat:

="New rent for "&A2&": "&TEXT(C2,"£#,##0.00")
 &" a month from "&TEXT(D2,"d mmmm yyyy")

A date written as 1 February 2027 cannot be misread as 2 January by a tenant used to American dates. That makes d mmmm yyyy the safer code for anything sent outside the office.

Scenario: Enter =TEXT(D2,"d mmmm yyyy") beside the start date and check it returns 1 February 2027. Then enter =TEXT(C2,"£#,##0.00") for the rent and confirm that 1014.3 now reads £1,014.30.

Try it in the grid

05TEXT Rounds the Words, Not the Rent

TEXT(C2,"£#,##0.00") turns 1319.625 into £1,319.63 in the notice. The rent in column C is still 1319.625, and that is what the rent ledger charges each month.

Seven of the fourteen new rents had a third decimal. Their notices promised one figure and the ledger expected another, half a penny apart.

FlatNew rent in CNotice with TEXT
Alder Ct 41319.625£1,319.63
Cedar Mews 21474.875£1,474.88
Fenn St 91226.475£1,226.48

The fix belongs in column C, not in the sentence. Round the rent where it is calculated, and every figure downstream agrees with the notice:

=ROUND(B2*1.035,2)

Scenario: Replace the new-rent formula in C2 with =ROUND(B2*1.035,2) and fill it down. Check that C3 now holds 1319.63 exactly, and that the notice and the cell agree on every row.

Try it in the grid

06Format Codes Depend on Excel's Language

The format code inside TEXT is a piece of text. Excel does not translate it when the workbook opens in a different language version, the way it translates function names.

Spanish Excel writes the year as aaaa and uses a comma for decimals, so its codes look like "d mmmm aaaa" and "#.##0,00". A code written for one language can come out wrong in the other, with no error.

If a workbook travels between countries, keep the date in the sentence unambiguous and short. Spelling the month in full avoids day-month confusion; the month name itself will follow the language of the Excel that calculates it.

07DOLLAR and TEXTJOIN

DOLLAR is a shortcut for money. =DOLLAR(C2,2) returns the amount with a currency sign, thousands separator and two decimals. The sign comes from the computer's regional settings, not from the workbook.

On Hollins' UK machines it writes £1,319.63. Opened by a contractor in Dublin it writes €1,319.63. Use TEXT with the sign in the code when the currency matters.

TEXTJOIN joins a list, and it drops formats exactly as & does. To list the start dates of several flats, wrap the range in TEXT first:

=TEXTJOIN(", ",TRUE,TEXT(D2:D4,"d mmm yyyy"))

Scenario: Enter =DOLLAR(C2,2) and =TEXT(C2,"£#,##0.00") side by side and compare them. Then check which currency sign DOLLAR chose, and decide which one belongs in a letter to a tenant.

Try it in the grid

08Check the Sentence Before It Leaves

Fourteen notices is few enough to read, and nobody read them. A formula can read them for you. A notice that names 2027 printed a date; one that does not printed a serial.

=ISNUMBER(SEARCH("2027",H2))

On the original notices that returns FALSE nine times. For the rents, a simple rule beats any check: build every number in a sentence with TEXT, and none can arrive with three decimals.

Scenario: Build the original notice in H2:H15, then enter =ISNUMBER(SEARCH("2027",H2)) in I2 and fill it down. Count the FALSE results, then rebuild H with TEXT and confirm every row turns TRUE.

Try it in the grid

09Six Traps

  1. Joining a date with &. It arrives as a serial number such as 46419.
  2. Joining money with &. The currency sign, comma and trailing zero all disappear, and every decimal comes through.
  3. Trusting the rows that look right. Dates stored as text join correctly and break everything else.
  4. Rounding only the words. TEXT rounds the notice; the cell still holds the unrounded figure.
  5. Format codes in another language. yyyy and aaaa are not interchangeable, and Excel will not translate them.
  6. DOLLAR on someone else's computer. The currency sign follows their regional settings, not yours.

10Four Exercises

  1. Reproduce it. Build the notice with & and confirm the serial number in the date.
  2. Find the text dates. Use COUNT and ISTEXT to find the five rows that are not real dates.
  3. Write it properly. Rebuild the notice with TEXT for both the rent and the date.
  4. Prove it. Round the rent with ROUND, then check every notice names 2027 and agrees with column C.

What to Take Away

Hollins' sheet was right. Every rent and every date in it was correct, and every cell displayed exactly what it should.

A format is how a cell looks, and & never sees it. When a number or a date goes into a sentence, write it with TEXT, round the figure where it is calculated, and read one finished sentence before it leaves the building.

Share this article:
Back to Blog
Daily challenge · Day 107

Total a Mileage Claim for One Pay Period with SUMIFS

A new exercise every day, solved in a real grid.

Solve today’s challenge