3.4Text/date functions & conditional logic (If/Select)
The essentials in 30 seconds
- Text + dates = 80% of needs: extract and clean with UpperCase, Left, Trim, ToText; extract periods and delays with Year, DateDiff, DateAdd, Today.
- If for thresholds, Select for codes: chained If…Then…Else for bands, Select…Case to translate ONE value into labels — more readable.
- IsNull first: an untested null field makes the formula fail or return empty — test IsNull before using any potentially empty field.
Running mini-case — Farès ranks his customers and watches late payments
Farès, management controller in Algiers, ranks his customers by hand (VIP, Standard, To follow) and computes payment delays with a calculator. He writes a band formula — If {Clients.CA} >= 500000 Then "VIP" Else If {Clients.CA} >= 100000 Then "Standard" Else "To follow" — and a delay in days with DateDiff("d", {Factures.Echeance}, CurrentDate), protected by an IsNull test on the payment date. The report categorizes and flags all by itself: segmentation happens on every refresh, with no spreadsheet.
This same case returns in the lesson, the diagram, the script and the 48h challenge.
1. Text and date functions: everyday formulas
Crystal Reports offers HUNDREDS of functions; day to day, two families are enough. TEXT functions: UpperCase / LowerCase (case), Left / Right / Mid (extract a part), Len (length), Trim (remove spaces), Replace (replace), ToText (convert number/date to text). They clean, extract and compose: a name initial (Left({Client.Nom}, 1)), a product code, a formatted label. DATE functions: Year / Month / Day (extract — essential for grouping by period), DateDiff (gap between two dates: a delay in days), DateAdd (add a duration: a due date), Today / CurrentDate (today's date). Delay = DateDiff("d", {Factures.Echeance}, CurrentDate).
These functions cover handling due dates, ages and monthly groupings — the daily bread of management reporting. Reflex: convert explicitly (ToText to display, CDate to convert text to date) rather than mixing types.
2. Conditional logic: If…Then…Else, Select…Case, IsNull
The heart of smart formulas. If…Then…Else returns a value according to a condition: If {Montant} > 1000 Then "Large" Else "Standard" — chainable for several bands (test from most restrictive to most general, the first true condition wins). Select…Case tests ONE value against several precise cases, more readably: Select {Clients.Statut} Case "A": "Active" Case "S": "Suspended" Default: "Unknown". Rule of thumb: THRESHOLDS and ranges → chained Ifs; ONE value compared to fixed cases (codes, categories) → Select…Case. The And, Or, Not operators combine conditions.
The reflex that avoids failures: test IsNull before using a potentially null field — a null date in a DateDiff, a null amount in a calculation, and the formula fails or returns empty. These formulas then feed display, groupings (3-5), conditional formatting (3-2) and filters: this is the vocabulary that makes reports tailor-made.
🔎 Concrete example — Customer segmentation and payment delay
Farès writes his two steering formulas in Crystal syntax.
Segment: If {Clients.CA} >= 500000 Then "VIP"
Else If {Clients.CA} >= 100000 Then "Standard" Else "To follow"
Delay: If IsNull({Factures.DatePaiement}) Then
DateDiff("d", {Factures.Echeance}, CurrentDate) Else 0Reading: thresholds → chained Ifs; possible null → IsNull test first.
Lesson: these categories will then be used to GROUP (3-5).
Section vocabulary
- Text (string) functions
- UpperCase/LowerCase, Left/Right/Mid (extract), Len, Trim (clean), Replace, ToText — to clean, extract, compose text.
Simple example: Farès's initial Left({Client.Nom}, 1).
What it is not: codes retyped by hand in a spreadsheet.
Reflex: extract and clean in formulas, never by hand.
- Date functions
- Year/Month/Day (extract a period), DateDiff (delay between two dates), DateAdd (add a duration), Today/CurrentDate.
Simple example: The delay DateDiff("d", due date, CurrentDate).
What it is not: delays computed with a calculator.
Reflex: Year to group, DateDiff for delays.
- If…Then…Else
- Returns a value according to a condition; chainable for several bands (first true condition = winner).
Simple example: The VIP / Standard / To follow segmentation.
What it is not: a Select…Case for amount thresholds.
Reflex: thresholds and ranges → chained Ifs.
- Select…Case
- Branching: tests ONE value against several precise cases + a default; more readable than Ifs for equalities.
Simple example: The status code translated into a label (Active, Suspended…).
What it is not: chained Ifs to translate ten fixed codes.
Reflex: one value, fixed cases → Select…Case.
- Null handling (IsNull)
- Test IsNull before using a potentially null field: an unhandled null makes the formula fail or return empty.
Simple example: The test on {Factures.DatePaiement} before the DateDiff.
What it is not: a direct DateDiff on a sometimes-empty date.
Reflex: IsNull on the first line of any at-risk formula.
In practice — Use functions and conditional logic
- Test TEXT functions: UpperCase, Left (extract), Trim (clean), and compose a label with ToText.
- Test DATE functions: Year({Date}) to extract the year, DateDiff for a delay in days.
- Write an If…Then…Else formula (categorize an amount); chain for several cases or use Select…Case for codes.
- Handle nulls with IsNull before using a field; check the syntax and test in preview.
Ready-to-say script — explain automatic segmentation (30 s)
“Segmentation is no longer manual: a formula ranks each customer — VIP, Standard or To follow — by revenue, and the delay computes itself from the due date, ignoring invoices already paid. On every refresh, the ranking is up to date.”
Say at the management committee; show the formula thresholds.
Copy-paste template — If, Select, dates (Crystal syntax)
// Bands (chained Ifs)
If {Clients.CA} >= 500000 Then "VIP"
Else If {Clients.CA} >= 100000 Then "Standard"
Else "To follow"
// Codes (Select…Case)
Select {Clients.Statut}
Case "A" : "Active"
Case "S" : "Suspended"
Default : "Unknown"
// Delay protected from nulls
If IsNull({Factures.DatePaiement}) Then
DateDiff("d", {Factures.Echeance}, CurrentDate)
Else 0Freelancer
Two formulas (one with bands, one with dates) and your report reasons all by itself.
SME in Algiers
Farès segments his customers and tracks delays with no calculator.
Project team
Document the thresholds (VIP, alerts) next to each formula to find them again.
Beginner
This week: one two-branch If + one IsNull-protected DateDiff.
✓ Best practices
- Test conditions from most restrictive to most general (the first true one wins).
- Prefer Select…Case as soon as a single value is compared to fixed cases.
- Put the IsNull test on the first line of any formula touching a doubtful field.
✗ Common pitfalls
- Chaining Ifs in the wrong order: a general condition swallows the special cases.
- Using Select…Case for thresholds (or Ifs for ten fixed codes): unreadable.
- Forgetting IsNull: the formula fails silently on records with an empty field.
Key takeaways
- TEXT: UpperCase/LowerCase, Left/Right/Mid, Len, Trim, Replace, ToText — extract, clean, compose.
- DATES: Year/Month (periods), DateDiff (delays), DateAdd (due dates), Today — delays, age, groupings.
- CONDITIONS: IF…THEN…ELSE (thresholds, chainable) and SELECT…CASE (one value, fixed cases) + And/Or/Not.
- Watch TYPES and NULLS: convert explicitly, test ISNULL before use.
48h challenge — apply tomorrow
Write 1 band formula on your real data + 1 delay calculation protected by IsNull, tested in preview.
- I handle text and dates in formulas with no external tool.
- I choose If or Select per case (thresholds vs codes).
- No doubtful field enters my formulas without an IsNull test.
Frequently asked questions
If…Then…Else or Select…Case: how to choose for categorizing?
Both return a different value according to data, but not for the same cases. IF…THEN…ELSE: two outcomes (“If {Montant} > 1000 Then “Large” Else “Standard””) or chained BANDS (“>= 5000: Very large”, “>= 1000: Large”, “>= 100: Medium”, else “Small”) — descending order, first true condition wins. Ideal for THRESHOLDS, ranges and combinations. SELECT…CASE: ONE value against PRECISE cases (“Select {Statut} Case “A”: “Active” Case “S”: “Suspended” Default: “Unknown””) — ideal for translating fixed codes and categories, more readable than Ifs. These computed categories are then used to GROUP (3-5): group by band, by translated status, by period.
How do I handle dates and null values?
DATES: extract with Year/Month/Day ({Date}) — to GROUP by period (yearly, monthly report); delay with DateDiff (days between order and delivery, delay vs due date, age); add with DateAdd (due date = date + 30 days); today with Today/CurrentDate (“as of today” delay); compare directly (Date > #2026-01-01#); convert with ToText/CDate. NULLS: a field can be NULL (empty — different from zero); an expression with a null can fail or return empty. Reflex: If IsNull({field}) Then … first — replacement value, or alternative calculation. Test systematically on payment dates, optional amounts and non-mandatory fields.
To translate ONE status code into labels (A → Active, S → Suspended…), the most readable is…