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
Former memberPosted Jan 28, 2016, 8:01 AM
Excel and SP Calc columns are totally different, AND you COPIED this material directly from MSDN: https://msdn.microsoft.com/en-us/library/office/bb862071(v=office.14).aspx
SriramPosted Jul 6, 2015, 1:27 AM
Excellent............
Vijay SPosted Apr 29, 2015, 3:09 AM
Good article
Karthik Muthu KaruppanPosted Apr 23, 2015, 11:20 AM
good one
Karthik Muthu KaruppanPosted Apr 23, 2015, 11:19 AM
good one
Krishnanand SivarajPosted Apr 22, 2015, 4:35 PM
Thanks for sharing
Santhakumar MunuswamyPosted Apr 22, 2015, 2:44 PM
Thanks for nice article
Rahul Kumar SaxenaPosted Apr 22, 2015, 2:30 PM
Good Show..
Sibeesh VenuPosted Apr 22, 2015, 7:14 AM
Nice
Ravinder sainiPosted Apr 22, 2015, 6:37 AM
nice
NitinPosted Apr 22, 2015, 5:28 AM
Nice