Save Time and Stay on Top of Your Construction Project with SharePoint’s Calculated Columns

Do you want to:

• Calculate the amount of retention on an invoice form?
• View all your inspection reports organized by month?
• Calculate all your Change Requests greater than $x requiring your approval?

It’s easy to get all that information, and more, with SharePoint’s calculated column feature.

Calculated columns (as the name implies) make calculations based on fields in a list or library.  You can add calculated columns to any list or library to do things like total or sub-total the value of documents and lists into a monthly view or add a condition to determine who needs to review a form.

The formulas used in the calculated fields are unique to SharePoint and are not necessarily the same as Excel formulas. Calculated fields only run when a record or item is saved, but custom event handlers can be added to trigger calculations if needed.

How to Add a Calculated Column in SharePoint

To add a calculated field to a list or library:

1. Select the List or Library where you want to add the calculated column and then click on “Create Column.” See #1 below.

This will pop up a modal dialog box showing you column selections.

Note: You can also click on “List Settings.” See #2 above. Then scroll down and click on “Create Column.” See the corresponding #2 below.

The “Settings: Create Column” page will display. See #3 below.

2. Type a name for the field and select the “Calculated (calculation based on other columns)” option.

Once you have made this selection, the options to configure this column will appear.

3. Enter a description if you want an explanation of the field as a reference. See #4 below.

4. Add a formula by selecting a column in the “Insert Column” box and then clicking on the “Add to formula” hyperlink. See #5 below.

Note: You build formulas from left to right (see samples below). Also, do not manually type column names in the Formula area. Instead select them using the “Insert Column” option. Once selected, Columns will be displayed within square brackets, e.g. [ColumnOne].

5. Select the data type (e.g., number or currency) you want the formula to display for the value of the field. See #6 below.

6. Choose whether you want to add the calculated column to the default view or not and select OK. See #7 below.

Frequently Used Formulas for Calculated Fields in SharePoint

Below are some sample formulas for calculated fields in SharePoint. You do not have to use these exact formulas. However, understanding the logic behind them may help you design your own calculations.

Sum values in a form

This formula is appropriate if you want to add up multiple columns in a form whenever you save the record (e.g. Change Order with line items that you want to total)

Note: Standard operators such as minus (-), plus (+), divided by (/) and multiplied by (*) will work. Make sure to enclose each individual calculation in parenthesis (not square brackets) if multiple calculations are required.

Formula:

=[Column1]+[Column2]

Example Data:

Column1: 10

Column2: 20

Example Result: 30

Document summarized by month

This calculation is helpful If you have multiple documents with a date field and you want to group the documents by month in a view (e.g. inspection reports grouped by date).  This calculation uses a built-in function TEXT(…) to apply formatting and the ampersand (&) to link the text together.

Formula:

• =TEXT(Date,”yyyy – “)&TEXT(Date,”mm”)&TEXT(Date,” (mmmm”)&TEXT(Date,” yyyy)”) )

Example Data:

• Date: 11/15/2017

Example Result:

• 2017 – 11 (November 2017)

Apply a condition if value is greater than

This formula is appropriate if you want to know whether an entered value is greater than a set value. For example, this could help you check Change Order requests over a certain amount, which might affect the approval cycle.

Formula:

• =IF([EnterValue]>[CheckValue], “Yes”, “No”)

Example Data:

• EnterValue: 20
• CheckValue: 10

Example Result:

• Yes

Get more tips and tricks for construction project management professionals

By now, we hope you see that calculated columns are a great way to keep your finger on the pulse of your construction management projects. The formulas above are just a small sample of what is available to help you manage your projects quickly and cost-effectively. The ability to do calculated columns is another one of the valuable capabilities that SharePoint makes possible in Construction Viz, our powerful construction project management software solution.

Contact us to see how Construction Viz can quickly, easily and cost-effectively make your job easier. You can also sign up for our monthly newsletter below to get our latest blog updates, tips on using Microsoft SharePoint, and other useful info delivered directly to your inbox.