SharePoint 2013: Calculated Column

SharePoint uses pretty much the same engine as Excel for it's calculated columns and field validation stuff.

There are some exceptions, such as NETWORKDAYS and some other functions not being included. Try prototyping your formulas in Excel first and then applying them to SharePoint.

SharePoint Calculated Columns are powerful tools when creating out-of-the-box solutions. With these columns, we can manipulate other columns in the list item.

The following are a few basic functions complete with details on how to utilize them.

There are some limitations, but we outline what they are and to work around them at the conclusion.

Microsoft SharePoint Foundation formulas for calculated fields are based on Microsoft Excel functions and syntax.

However, Microsoft supports only those functions specified on this page for use in SharePoint Foundation calculated fields. For example, the Excel function MID is not supported.

Calculated Columns

The following is a screen capture of where you enter the formula for a Calculated column. See that big empty space to the left.


Important: All example formulas in this topic use commas "," as the parameter delimiter character.

Examples

Conditional formulas

You can use the following formulas to test the condition of a statement and return a Yes or No value, to test an alternate value such as OK or Not OK, or to return a blank or dash to represent a null value.

Use the IF function to perform this comparison.

Formula Description
=[Column1]>[Column2] Is Column1 greater than Column2? (Yes)
IF([Column1]<=[Column2], "OK", "Not OK") Is Column1 less than or equal to Column2? (Not OK)
=AND([Column1]>[Column2], [Column1]<[Column3]) Is 15 greater than 9 and less than 8? (No)
=OR([Column1]>[Column2], [Column1]<[Column3]) Is 15 greater than 9 or less than 8? (Yes)

Date and time formulas

To add a number of days to a date, use the addition (+) operator.

Formula Description
=[Column1]+[Column2]
Example:
Column1=6/9/2007
Colimn2=3
Adds 3 days to 6/9/2007 (6/12/2007)

To add a number of months to a date, use the DATE, YEAR, MONTH and DAY functions.

Formula Description
=DATE(YEAR([Column1]),MONTH([Column1])+[Column2],DAY([Column1]))
Example:
Column1=6/9/2007
Colimn2=3
Adds 3 months to 6/9/2007 (9/9/2007)

To add a number of years to a date, use the DATE, YEAR, MONTH and DAY functions.

Formula Description
=DATE(YEAR([Column1])+[Column2],MONTH([Column1]),DAY([Column1]))Example:
Column1=6/9/2007
Colimn2=3
Adds 3 years to 6/9/2007 (6/9/2010)

Calculate the difference between two dates

Use the DATEDIF function to perform this calculation.

Formula Description
=DATEDIF([Column1], [Column2],"d")
Example:
Column1=01-Jan-1995
Colimn2=15-Jun-1999
Returns the number of days between the two dates (1626)
=DATEDIF([Column1], [Column2],"ym")
Example:
Column1=01-Jan-1995
Colimn2=15-Jun-1999
Returns the number of months between the dates, ignoring the year part (5)
=DATEDIF([Column1], [Column2],"yd")
Example:
Column1=01-Jan-1995
Colimn2=15-Jun-1999
Returns the number of days between the dates, ignoring the year part (165)

Calculate the difference between two times

Formula Description
=TEXT([Column2]-[Column1],"h:mm:ss")
Example:
Column1=06/09/2007 10:35 AM
Colimn2=06/09/2007 3:30 PM
Hours, minutes and seconds between two times (4:55:00)

Mathematical formulas

Add/Subtract and Multipyl/Divide the two column numbers.

Formula Description
=[Column1]+[Column2]+[Column3]
Example:
Column1=4
Colimn2=5
Column3=6
Adds the values in the first three columns (15)
=[Column1]-[Column2]
Example:
Column1=7
Column2=5
Subtracts 5 from7 (2)
=[Column1]*[Column2]
Example:
Column1=7
Column2=5
Multiplies the numbers in the first two columns
=[Column1]/[Column2]
Example:
Column1=15
Column2=5
Divides

Change the case of text.

Formula Description
=UPPER([Column1])
Example:
Column1=Gowtham RAJAMANICKAM
Changes text to uppercase (GOWTHAM RAJAMANICKAM)
=LOWER([Column1])
Example:
Column1=Gowtham RAJAMANICKAM
Changes text to uppercase (gowtham rajamanickam)

Combine first and last names

Formula Description
==[Column1]&[Column2]
Example:
Column1=Gowtham
Column2= RAJAMANICKAM
Combines the two strings (GOWTHAM RAJAMANICKAM)

Notes

  • Calculated columns can only interact with data in the same “item” such as an item is a single task, event, document and so on
  • Calculated columns cannot interact with another row, or summaries (total and so on) of all of the lists
  • The formulas you write are basically Excel compatible formulas
  • Calculated columns can be reused by creating them as Site Columns (but this column can only reference other Site Columns!)
  • Column names with spaces or symbols must be enclosed in brackets “[Sale Amount']”
  • The [TODAY] and [ME] functions do not work in a calculated column, but can be used in columns referenced by a calculated column

Reference: msdn