Outlook Formulas: Productive and Fun!

Outlook lets you use formulas to create new fields in an Outlook folder and to validate the values on an Outlook form. For example, you can use a formula to add a field to the Contacts folder that gives the current age for a contact with an entry in the Birthday field. On a form, you might want to require the user to enter a value for a particular property, but only if a second property has a certain value.

Unfortunately, Microsoft provides no documentation about constructing such formulas. In a three-part series beginning this week, I'll give you a few examples to get you off to a good start. Before you become too excited, though, here's some bad news: You can't use formula fields to sort, group by, or filter a folder view. This means, for example, that you can't use a custom formula field to group your Journal folder by the month in which entries were made.

Let's start with a formula field in a folder. In a table view, right-click the column headings and choose Field Chooser. To create a new formula field, click the New button on the Field Chooser, choose the Formula type for the new field, and click the Edit button to bring up the Field and Function lists that provide field names and available functions you can use to build your formula. It's similar to the expression builder in Microsoft Access, but much simpler.

For example, you could show when you added any particular contact by creating a Year Added field for your Contacts folder. After you click Edit and see the Formula Field dialog, click the Function button, then Date/Time, then Year. This pastes "Year( date )"—the syntax for the Year function—into the Formula box. Next, select the word "date" in the formula, then click the Field button, then All Contact Fields, and select the Created field. The formula should look like this:

Year([Created]) 

The IIf() function for returning two different values, based on whether a condition is true or not, resembles certain Excel formulas. Here's an Outlook example: Let's say you're using the built-in Mileage field on a custom appointment form to track the miles traveled to and from meetings. You might want to add a Mileage Expense formula field to calculate travel costs, but leave the Mileage Expense field blank if the Mileage field is blank. If you use 33 cents per mile to calculate your expense, try this formula:

IIf([Mileage]>0,"$" & [Mileage]*0.33,"")

Try this more complicated example. Use the IIf() function in a formula field in a Contacts folder. The formula calculates your contacts' current age (except those born on Feb. 29 of a leap year):

IIf([Birthday] <> "None", DateDiff("yyyy", [Birthday], Date()) -IIf(DateDiff("d", CDate(Month([Birthday]) & "/" & Day([Birthday]) & "/" & Year(Date())),Date()) < 0, 1, 0),"") 

Combination fields, like formula fields, can concatenate data in a view. Let's say that you have some contacts with only names, some with only the company name, and some with both. You want to create a Contacts folder view that shows either the person's name or the company name. Create a new field named "Total Name" and select Combination as the type. Click the Edit button, and then enter this formula:

[Full Name] [Company]

Select "Showing only the first non-empty field, ignoring subsequent ones." Like formula fields, this method doesn't let you sort, group, or filter by combination fields.

Next week, we'll use formulas in Outlook forms to validate data, set default values, and automatically calculate Outlook properties values. In the meantime, send me any great Outlook formulas you've written that you'd like to share.