2.3Text & date/time functions
The essentials in 30 seconds
- Extract then assemble: LEFT/RIGHT/MID split, & and CONCAT rejoin — TRIM cleans in between.
- A date is a number: End − Start = days, TODAY() = current day — display the result as Number.
- Clean before crossing: spaces and case make XLOOKUPs fail — TRIM + UPPER first.
Running mini-case — Wassim and the duplicate suppliers
Wassim (2.2) sees his XLOOKUPs miss suppliers that are actually present: «SARL Atlas» with two spaces, «sarL atlas» in lowercase, due dates in text that refuse to calculate. He cleans: TRIM removes the stray spaces, PROPER harmonizes the case, and his cross-references finally notice. Then he computes the delays: due date minus today in days, age of unpaid items with DATEDIF. In 2.4, these computed delays will drive the alert colors; in 2.5, everything will live in a structured table.
Continuation of the 2.2 thread: Wassim's data becomes clean and calculable — and continues in 2.4 and 2.5.
1. Extracting and assembling text
LEFT, RIGHT, MID extract pieces; LEN counts the characters; TRIM removes extra spaces; UPPER/LOWER/PROPER change the case. To assemble, the & operator or CONCAT: =A2&" "&B2 glues first and last names. Wassim builds a clean supplier code: initial + cleaned name + wilaya, all in uppercase.
2. Dates are numbers
A date is a day number: you can therefore calculate with it. =B2-A2 gives a number of days; =TODAY() returns today's date; YEAR/MONTH/DAY extract the components. DATEDIF computes gaps in years/months/days: =DATEDIF(A2, TODAY(), "y") for an age in years. Wassim computes days late = due date − today, and displays the result in Number format (otherwise Excel displays a fake date).
3. Cleaning before analyzing
Imported data is often «dirty»: invisible spaces, inconsistent case, dates as text. The reflex: TRIM for spaces, PROPER for case, VALUE/DATEVALUE to convert numbers and dates stored as text back. Wassim cleans the supplier names BEFORE his XLOOKUPs (2.2): two columns homogenized on both sides, otherwise the «identical» key stays unfindable. Clean first, analyze next.
🔎 Concrete example — 🔎 Concrete example — Wassim's late payment
Due date in B2; Wassim computes the delay and builds a clean code (initial in A2, name in C2):
=B2-TODAY() → display in Number format (days remaining)
=UPPER(LEFT(A2,1)) & "." & UPPER(TRIM(C2))
=DATEDIF(A2, TODAY(), "y") → age in yearsReading: the date subtraction gives days; LEFT + TRIM + & build an identifier without hidden spaces.
Lesson: a date result displayed as Date is a format to fix, not a calculation to throw away.
Section vocabulary
- TRIM
- Removes leading, trailing and internal duplicate spaces: the weapon against «identical» keys that fail.
Simple example: Wassim cleans «SARL Atlas» before his XLOOKUP.
What it is not: a formatting that colors the cells.
Reflex: clean on both sides before crossing.
- & / CONCAT
- Assemble several texts into a single string: =A2&" "&B2.
Simple example: Wassim glues initial, dot and name into one code.
What it is not: the addition + reserved for numbers.
Reflex: assemble with &, not with +.
- TODAY
- Returns the system date, recalculated on each opening: the reference for delays and due dates.
Simple example: Wassim compares each due date to TODAY().
What it is not: a frozen date typed by hand.
Reflex: a living reference for deadlines.
- DATEDIF
- Gap between two dates in years ("y"), months ("m") or days ("d"): ages and seniority.
Simple example: Wassim computes the age of an unpaid item with =DATEDIF(A2,TODAY(),"y").
What it is not: a simple subtraction displayed as a date.
Reflex: calendar gaps = DATEDIF.
In practice — In practice — Clean then calculate
- Clean a column of names with TRIM + PROPER.
- Assemble a clean code with LEFT and & (initial + name).
- Compute days remaining: =DueDate-TODAY(), Number format.
- Compute an age in years with DATEDIF.
Ready-to-say script — explaining the cleaning (30 s)
« Our suppliers existed twice because of invisible spaces and different cases: I clean everything with TRIM and PROPER before crossing, and the lookups notice. The due dates are real numbers, so the delay calculates by itself in days. Clean data first, reliable analyses next. »
Say it while showing Wassim's duplicates merging after cleaning.
Copy-paste template — cleaning + dates
=TRIM(A2)
=PROPER(TRIM(A2))
=B2-TODAY()
=DATEDIF(A2, TODAY(), "y")Duplicate keys
«SARL Atlas» vs «sarL atlas»: Wassim applies TRIM + UPPER on both sides and the duplicates merge.
Date as text
An imported due date refuses to calculate: Wassim converts it with DATEVALUE, then subtracts normally.
Fake result date
A delay displays «Jan-1900»: Wassim switches the cell back to Number format — the calculation was right.
Clean code
Identifying each supplier with a short code: Wassim combines LEFT, TRIM, UPPER and & in one formula.
✓ Best practices
- Clean (TRIM, case) before any cross-reference.
- Assemble with & (fast) or CONCAT.
- Remember that a date = a number: result in Number format.
✗ Common pitfalls
- Believing that a date in text calculates (you need DATEVALUE).
- Forgetting the invisible spaces that make XLOOKUPs fail.
- Extracting by fixed position (LEFT/RIGHT) on texts of variable length.
Key takeaways
- LEFT/RIGHT/MID extract; LEN counts; TRIM cleans.
- & and CONCAT assemble text.
- A date is a number: End − Start = days; DATEDIF = gaps.
- Clean before analyzing (spaces, case, text to number/date).
48h challenge — apply tomorrow
On a real column (suppliers, clients): TRIM + case applied, a code assembled with &, then a delay in days computed and displayed as Number.
- I can clean text (TRIM, case) before any cross-reference.
- I can assemble (LEFT, &, CONCAT) clean identifiers.
- I calculate with dates (subtraction, TODAY, DATEDIF) in Number format.
Frequently asked questions
Why does my date subtraction give a date instead of a number?
The result cell inherited a Date format. The value (number of days) is correct: switch the cell to Number format to display it as an integer.
My XLOOKUPs fail on «identical» keys?
Often invisible spaces or a different case. Apply TRIM (and possibly UPPER) on both sides to homogenize before crossing.
Which operator assembles the content of two text cells?