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 2027 | Figure |
|---|---|
| Notices sent | 14 |
| Dates printed as 46419 | 9 |
| Rents quoted to three decimals | 7 |
| Monthly increase on the nine flats | £373.30 |
| Months lost | 2 |
| Rent lost | £746.60 |
Scope.
TEXT,ROUND,DOLLARand the&operator work in every version of Excel.TEXTJOINin 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.
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 shows | Value underneath | Joined with & |
|---|---|---|
| 01/02/2027 | 46419 | 46419 |
| £1,319.63 | 1319.625 | 1319.625 |
| £1,014.30 | 1014.3 | 1014.3 |
| 3.5% | 0.035 | 0.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.
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.
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.
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.
| Flat | New rent in C | Notice with TEXT |
|---|---|---|
| Alder Ct 4 | 1319.625 | £1,319.63 |
| Cedar Mews 2 | 1474.875 | £1,474.88 |
| Fenn St 9 | 1226.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.
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.
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.
09Six Traps
- Joining a date with &. It arrives as a serial number such as 46419.
- Joining money with &. The currency sign, comma and trailing zero all disappear, and every decimal comes through.
- Trusting the rows that look right. Dates stored as text join correctly and break everything else.
- Rounding only the words.
TEXTrounds the notice; the cell still holds the unrounded figure. - Format codes in another language.
yyyyandaaaaare not interchangeable, and Excel will not translate them. DOLLARon someone else's computer. The currency sign follows their regional settings, not yours.
10Four Exercises
- Reproduce it. Build the notice with & and confirm the serial number in the date.
- Find the text dates. Use
COUNTandISTEXTto find the five rows that are not real dates. - Write it properly. Rebuild the notice with
TEXTfor both the rent and the date. - 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.