Column Types: Calculated Field

Office 365 / SharePoint Blog

One of my favorites is the Calculated field. This is where we can concatenate and run IF/THEN statements, and a number of other formulae that you can use in Excel. I am a fan of anything that can be automated, so when I can automatically make calculations or add the info from multiple fields together, I’m a happy gal.

In addition to the basic formula options Microsoft suggests below, I have used the calculated field to provide an automatic start date based on duration, or if the standard lead time is 7 days, but certain circumstances require a longer lead time, then I used an IF statement to see if the lead time had a value, and if so, to calculate the start date by taking the due date and subtracting the lead time, but if there was no lead time specified, to default to subtracting 7 days from the due date.

The formula looked like this:
=IF(ISBLANK([Publish Date]),””,IF([Lead Time],[Publish Date]-[Lead Time],[Publish Date]-7))

A caveat for SPOCKs: if you plan on using Content Types, the calculated fields apparently don’t travel with the Content Types when you add them to existing lists – you have to add the site columns manually before you import the Content Types. For more information about Content Types, please contact me.
Microsoft tells us the following about this field:
Use this column field type to display information that is based only on the results of a calculation.

When you add a calculated column to a list or library, you create a formula that contains operators, such as subtraction (-) and addition (+), functions, specific values, and references to other columns. Formulas can calculate dates and times, perform mathematical equations, or manipulate text. For example, on a Tasks list, you can use this type of column to calculate the number of calendar days required to complete each task based on the Start Date and Date Completed columns (=[Date Completed]-[Start Date]). In a Contacts list, you can use a Calculated column to combine the first and last names of the contacts and separate them with a space, based on First Name and Last Name columns (=[First Name]&” “&[Last Name]). Note that the formula in a calculated column can only reference other columns in the same list or library.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s