Examples of Conditional Expressions

The following examples illustrate complex formula user-defined fields using conditional expressions.

The example Date UDF used is [Creation Date].

Example 1

If ([Creation Date] Date(2009, 7, 9), "New", "Old")

This example compares a date field with a constant date, using the Date function to format the date properly. The formula returns an alphanumeric value. If the date is later than the supplied date, the field has a value of “New”. If the date is earlier than the supplied date; the field has a value of “Old”.

 

Example 2

If(Not(Search("Canada",[Country])0 Or Search("USA",[Country])0 Or 
Search(“Mexico”,[Country])0),"Other","North America")

This example inspects the Country field of an Address Book entry to determine if the entry is located in North America or not. It uses the alphanumeric Search function to look for the countries “Canada”, “USA”, or “Mexico”. If none of those strings are found in the entry’s Country field, the formula evaluates to “Other”, but if one of those strings is found it outputs “North America”.

 

Example 3

If (Dayofweek([Creation Date]) == 1, "Sunday",
If (Dayofweek([Creation Date]) == 2, "Monday",
If (Dayofweek([Creation Date]) == 3, "Tuesday",
If (Dayofweek([Creation Date]) == 4, "Wednesday",
If (Dayofweek([Creation Date]) == 5, "Thursday",
If (Dayofweek([Creation Date]) == 6, "Friday",
If (Dayofweek([Creation Date]) == 7, "Saturday", " ")))))))

This example uses the Dayofweek function to get the day of the week expressed as an integer value for a date field. It compares the value with integer values and outputs a string representing the day of the week in. If it does not have a value; it outputs an empty string.

Example 4

if(Day[Creation Date]<10,"0"+Text(Day[Creation Date]),Text(Day([Creation Date])))
+" "+
if(Month([Creation Date])==1,"Jan",if(Month([Creation Date])==2,"Feb",
if(Month([Creation Date])==3,"Mar",if(Month([Creation Date])==4,"Apr",
if(Month([Creation Date])==5,"May",if(Month([Creation Date])==6,"Jun",
if(Month([Creation Date])==7,"Jul",if(Month([Creation Date])==8,"Aug",
if(Month([Creation Date])==9,"Sep",if(Month([Creation Date])==10,"Oct",
if(Month([Creation Date])==11,"Nov",if(Month([Creation Date])==12,"Dec",
"Unknown Month"))))))))))))

This example formats an alphanumeric UDF such that it displays the following format of a date UDF: 

eg. 15 Jan