Formula 2

Operators/Functions Used

If-Then-Else, Subscript [], Less Than (<), Concatenate (+), Make range (to), ToNumber, Subtract (-), ToText, Parentheses ()

Formula Purpose

You have the date of first purchase coded as the fourth and fifth characters of the customer number (for example, 1971 as the 71 in ABC7101234, 1988 coded as the 88 in ABC880544, etc.) and you want to customize a letter to thank customers for the number of years they have done business with you. You want the following sentence to appear in your letter:

"You have been a valued customer for" [x] "years."
«Where x is the number of years.»

Formula

If {file.CUSTOMER NUMBER}[4 to 5] < "90" Then
"You have been a valued customer for " + ToText(91
- ToNumber({file.CUSTOMER NUMBER}[4 to 5]))
+ " years."
Else 
"You are one of our newer customers, and we want you to 
know how valuable you are to us."

Result

Customer #	Resulting Sentence
ABC7801234	"You have been a valued customer for 13 years."
ABD890337	"You have been a valued customer for 2 years."
ABD904331	"You are one of our newer customers, and we want you to know how valuable you are to us."

Explanation

• The If-Then-Else expression says, "If the 4th and 5th elements of the customer number, expressed as numbers, are less than 90, print a sentence including the date of first purchase, otherwise print the 'newer customer' sentence."
• The formula above uses the Subscript [ ] operator to extract the 4th and 5th characters (your date code) from the customer numbers which are stored as text in character fields. The Make Range operator (to) is used to establish the range 4 to 5.
• In the first example (ABC7801234) the 4th and 5th digits are 78 representing the year of first purchase as 1978.
• In the second example (ABD8903337), the 4th and 5th digits are 89 representing the year of first purchase as 1989.
• If the extracted characters are less than "90" (Then), a concatenated text string (a sentence) is printed that is customized to indicate the number of years the individual has been a customer. The text string says, "You have been a valued customer for (calculated number, expressed as text) years."
• The calculation of the number of years as a customer involves several steps:
  • _        As was done earlier, the Subscript [ ] operator extracts the 4th and 5th characters (your date code) from the customer numbers which are stored as text in character fields. The Make Range operator (to) is used to establish the range 4 to 5.
  • _    ToNumber converts the extracted date code to a number so it can be used in the calculation 91-x, where x = the date code expressed as a number.
  • _    91-x subtracts the year of first purchase from 91 (the current year) to get the number of years the individual has been a customer.
  • _    ToText then converts the result of that calculation back to text so it can be used in the expression "You have been a valued customer for (x) years."
• If the characters are "90" or more (Else), the fixed text string "You are one of our newer customers, and we want you to know how valuable you are to us" is printed.
• The Parentheses () control the order of calculation of the formula.

Related topics

Formulas In Action