Recent

SharePoint Calculated Column Examples

This article covers the following types of Calculated Column formulas:

  • Date and time formulas
  • Mathematical (number field) formulas
  • Text formulas

Click on the links below to go to the tutorials for:

**Most of the formulas in this tutorial were copied from the following Microsoft MSDN article:
https://msdn.microsoft.com/en-us/library/office/bb862071(v=office.14).aspx

I’ve tried to simplify the formulas from the MSDN article and include screenshots of the formulas to better show how these formulas could be used.

Note Note
When creating calculated columns make sure the correct returned data type is selected, screenshots have been provided for each group of formula examples.

To add a number to a date, use the addition (+) operator.  To subtract a number use the (-) operator.

Add or Subtract days from dates

Column1 (Date) Column2 (Number) Formula Description (possible result)
6/9/2016 3 =[Column1]+[Column2] Adds 3 days to 6/9/2016
(6/12/2016)
12/10/2016 =[Column1]-4 Subtracts 4 days from 12/10/2016 (12/6/2016)

SP-Calculated-Column-Examples-16-6

Add or Subtract Years, Months or Days from dates

Use the DATE, YEAR, MONTH, and DAY functions.

Column1
(Date)
Column2
(Number)
Formula Description (possible result)
6/9/2016 3 =DATE(YEAR([Column1])+[Column2],MONTH([Column1]),DAY([Column1])) Adds 3 months to 6/9/2016 (9/9/2016)
2/10/2016 5 =DATE(YEAR([Column1])+3,MONTH([Column1])+[Column2],DAY([Column1])+1) Add 3 years, 5 months, and 1 day to 2/10/2016 (7/11/2019)

Calculate the difference between two dates

Use the DATEDIF function, the data type returned must be a Number.

Column1 Column2 Formula Description (possible result)
01/01/2016 06/15/2016 =DATEDIF([Column1], [Column2],”d”) Returns the number of days between the two dates (166)
08/01/2014 01/01/2016 =DATEDIF([Column1], [Column2],”m”) Returns the number of months between the dates (17)

SP-Calculated-Column-Examples-16-7

Calculate the difference between two times

Use the INT function to subtract one date/time from another, then times that number by the number of whatever units (hours, minutes, seconds) in a Day.  The data type returned must be a Number.

Column1 Column2 Formula Description (possible result)
06/09/2016 10:35 AM 06/10/2016 3:30 PM =INT(([Column2]-[Column1])*24) Total hours between two times (28)
06/09/2016 10:35 AM 06/10/2016 3:30 PM =INT(([Column2]-[Column1])*1440) Total minutes between two times (1735)
06/09/2016 10:35 AM 06/10/2016 3:30 PM =INT(([Column2]-[Column1])*86400) Total seconds between two times (104100)

Show dates as the day of the week

To convert dates to the text for the day of the week, use the TEXT and WEEKDAY functions. The data type returned must be a Single line of text.

Column1 Formula Description (possible result)
04/16/2016 =TEXT(WEEKDAY([Column1]), “dddd”) Calculates the day of the week for the date and returns the full name of the day (Saturday)
04/21/2016 =TEXT(WEEKDAY([Column1]), “ddd”) Calculates the day of the week for the date and returns the abbreviated name of the day (Thu)

SP-Calculated-Column-Examples-16-8

Mathematical (number) formulas


You can use the following formulas to perform a variety of mathematical calculations, such as adding, subtracting, multiplying, dividing numbers; calculating the average or median of numbers; and doing greater or less than comparisons.

Add or Subtract numbers

To add numbers in two or more columns in a row, use the addition (+) or subtraction (-) operator

ColumnA ColumnB ColumnC Formula Description (possible result)
15 10 5 =[ColumnA]+[ColumnB]+[ColumnC] Adds 15+10+5 together (30)
15 10 5 =[ColumnA]-[ColumnB]-[ColumnC] Subtracts 15-10-5 (0)

SP-Calculated-Column-Examples-16-4

Multiply or Divide numbers

Column1 Column2 Formula Description (possible result)
5 2 =[Column1]*[Column2] Multiplies 5 * 2
(10)
15000 12 =[Column1]/ [Column2] Divides 15000 by 12 (1250)

Calculate the average of numbers

The average is also called the mean. To calculate the average of numbers in two or more columns in a row, use the AVERAGE function.

Column1 Column2 Column3 Formula Description (possible result)
6 5 4 =AVERAGE([Column1], [Column2],[Column3]) Average of the numbers in the first three columns (5)
6 5 4 =AVERAGE(IF([Column1]>[Column2], [Column1]-[Column2], 10), [Column3]) If Column1 is greater than Column2, calculate the average of the difference and Column3. Else calculate the average of the value 10 and Column3 (2.5)

Calculate the smallest or largest number in a range

To calculate the smallest or largest number in two or more columns in a row, use the MIN and MAX functions.

Column1 Column2 Column3 Formula Description (possible result)
10 7 9 =MIN([Column1], [Column2], [Column3]) Smallest number (7)
10 7 9 =MAX([Column1], [Column2], [Column3]) Largest number (10)

Calculate the difference between two numbers as a percentage

Use the subtraction (-) and division (/) operators and the ABS function.

Column1 Column2 Formula Description (possible result)
2342 2500 =([Column2]-[Column1])/ABS([Column1]) Percentage change (6.75%)

Increase or decrease a number by a percentage

Use the percent (%) operator to perform this calculation.

Column1 Column2 Formula Description (possible result)
100 =[Column1]*(1.05) Increases number in Column1 by 5% (105)
100 7% (Column 2 must be number field set to display as percentage) =[Column1]*(1+[Column2]) Increases number in Column1 by the percent value in Column2: 7% (107)
100 7% (Column 2 must be number field set to display as percentage) =[Column1]*(1-[Column2]) Decreases number in Column1 by the percent value in Column2: 7% (93)

SP-Calculated-Column-Examples-16-9

Determine whether a number is greater than or less than another number

Use the IF function to compare number fields, the data type returned must be a Single line of text.

Column1 Column2 Formula Description (possible result)
15 9 =IF([Column1]>[Column2], “Yes”, “No”) Is Column1 greater than  Column2? (Yes)
15 9 =IF([Column1]<=[Column2], “OK”, “Not OK”) Is Column1 less than or equal to Column2? (Not OK)

SP-Calculated-Column-Examples-16-10

Number conditions with an AND or OR functions

Using the AND or OR functions your can create more complex conditional formulas

Column1 Column2 Column3 Formula Description (possible result)
15 9 8 =IF(AND([Column1]>[Column2], [Column1]<[Column3]), “OK”, “Not OK”) If 15 is greater than 9 AND 15 is less than 8, return “OK”. (Not OK)
15 9 8 =IF(OR([Column1]>[Column2], [Column1]<[Column3]), “Yes”, “No”) If 15 is greater than 9 OR if 15 is less than 8, return “Yes”. (Yes)

Text formulas


You can use the following formulas to manipulate text, such as combining or concatenating the values from multiple columns, comparing the contents of columns, and removing characters or spaces.

Change the case of text

To change the case of text, use the UPPER, LOWER, or PROPER function.

Column1 Formula Description (possible result)
John Doe =UPPER([Column1]) Changes text to uppercase (JOHN DOE)
John Doe =LOWER([Column1]) Changes text to lowercase (john doe)
jane doe =PROPER([Column1]) Changes text to title case (Jane Doe)

SP-Calculated-Column-Examples-16-11

Combine text column values together

To combine first and last names, use the ampersand operator (&) or the CONCATENATE function.

Column1 Column2 Formula Description (possible result)
Carlos Carvallo =[Column1]&[Column2] Combines the two strings (CarlosCarvallo)
Carlos Carvallo =[Column1]&” “&[Column2] Combines the two strings, separated by a space (Carlos Carvallo)
Carlos Carvallo =[Column2]&”, “&[Column1] Combines the two strings, separated by a comma and a space (Carvallo, Carlos)

Combine text and numbers from different columns

To combine text and numbers, use the CONCATENATE function, the ampersand operator (&), or the TEXT function and the ampersand operator.

Column1 Column2 Formula Description (possible result)
Yang 28 =[Column1]&” sold “&[Column2]&” units.” Combines contents above into a phrase (Yang sold 28 units.)
Dubois 40% =[Column1]&” sold “&TEXT([Column2],”0%”)&” of the total sales.” Combines contents above into a phrase (Dubois sold 40% of the total sales.)

Note   The TEXT function appends the formatted value of Column2 instead of the underlying value, which is 0.4.

Combine text with a date or time

To combine text with a date or time, use the TEXT function and the ampersand operator (&).

Column1 Column2 Formula Description (possible result)
06/20/2016 =”Payment due: “&TEXT([Column2], “mmmm dd, yyyy”) Combines text with a date (Payment due: June 20, 2016)
Billing Date 06/20/2016 =[Column1]&” “&TEXT([Column2], “mmm-dd-yyyy”) Combines text and date from different columns into one column (Billing Date Jun-05-2016)

Determine whether a column value or a part of it matches specific text

To determine whether a column value or a part of it matches specific text, use the IF, FIND, SEARCH, and ISNUMBER functions.

Column1 Formula Description (possible result)
Urgent =IF([Column1]=”Urgent”, “Yes”, “No”) Determines whether Column1 is Urgent (Yes)
Standard =IF(ISNUMBER(FIND(“s”,[Column1])), “OK”, “Not OK”) Determines whether Column1 contains the letter s (OK)
BD123 =ISNUMBER(FIND(“BD”,[Column1])) Determines whether Column1 contains BD (Yes)

Remove characters from text

To remove characters from text, use the LEN, LEFT, and RIGHT functions.

Column1 Formula Description (possible result)
Vitamin A =LEFT([Column1],LEN([Column1])-2) Returns 7 (9-2) characters, starting from left (Vitamin)
Vitamin B1 =RIGHT([Column1], LEN([Column1])-8) Returns 2 (10-8) characters, starting from right (B1)

Remove spaces from the beginning and end of a column

To remove spaces from a column, use the TRIM function.

Column1 Formula Description (possible result)
     Hello there! =TRIM([Column1]) Removes the spaces from the beginning and end (Hello there!)

About Nick Hurst (84 Articles)
SharePoint and Nintex developer who strives to find easy to deploy and maintain solutions for business problems.

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

%d bloggers like this: