I thought it would be a good idea to post this list of common formulas for SharePoint Calculated Columns. I have found myself coming back to this page a lot, over the years. I hope it might help you as well. There are a ton of other common ones, I’m sure. If you think of any good ones to add please let me know at dustin@dustinschieler.com!
| Type | Explanation | Output |
| NUMBERS | ||
| Profit | Shows the percentage profit on a sale (tick “Show as percentage”) | 10% |
| ([Price]-[Cost])/[Cost] | ||
| Markup | Gives a price from a cost and a percentage markup | $120.00 |
| [Cost]*(1+[Markup]) | ||
| Commission | Gives the commission due on a sale (based on a commission %) | $25.00 |
| [Sale]*[Commission] | ||
| Formatting | Formatted with $ currency, comma thousand separator and 2 decimal places | $1234.56 |
| TEXT([Sales],”$#,###.00?); | ||
| Negative numbers in brackets | (95.99) | |
| TEXT([Sales],”#,###.00;(#,###.00)”); | ||
| OPERATORS | ||
| & | Concatenate (put two text values or fields together) 4 & “3? | 43 |
| ^ | Power (e.g. [Field]^2 = Squared) 4^3 | 64 |
| /, +, -, * | Divide, Add, Subtract, Multiply | |
| RELATIONAL OPERATORS | ||
| = (Equal to) | > (Greater than) | >= (Greater than or equal to) |
| <> Not equal to) | < (Less than) | <= (Less than or equal to) |
| DATE AND TIME | ||
| Time only | TEXT([DateTimeField],”hh:mm:ss”) | 01:21:51 |
| Weekday | TEXT([DateField],”dddd”) | Wednesday |
| TEXT([DateField],”ddd”) | Wed | |
| Month | TEXT([DateField],”mmmm”) | October |
| TEXT([DateField],”mmm”) | Oct | |
| Year | TEXT([DateField],”yyyy”) | 2012 |
| TEXT([DateField],”yy”) | 12 | |
| Combinations | TEXT([DateField],”mmmm dd, yyyy” | October 17, 2012 |
| Fiscal Year | Shows which fiscal year a date falls in (1st October) | |
| FY & IF(DATE(YEAR([Date]), 10, 1)>[Date], YEAR([Date]), YEAR([Date])+1) | FY 2012 | |
| Season | Shows which season a date falls in. Takes into account one month offset from quarter. | |
| CHOOSE(INT((MOD(MONTH(When)+1,12)/4))+ 1,”Winter”,”Spring”,”Summer”,”Autumn”) | Spring | |
| Quarter | Shows which quarter a date falls in | |
| Q & INT((MONTH([Date])-1)/3)+1 | Q1 | |
| Q & INT((MONTH([Date])-1)/3)+1 & “-” & YEAR([Date]) | Q1-2012 | |
| Week Number | Shows the week number (US style) | |
| ROUNDDOWN(([Date]-DATE(YEAR([Date]),1,1)+ WEEKDAY(DATE(YEAR([Date]),1,1))-WEEKDAY([Date])+1)/7,0)+1 | 5 | |
| Week Commencing | Shows the date of the first day of the week (useful for grouping by week) | |
| [Date]-WEEKDAY([Date])+1 | 3/4/2012 | |
| Day/Night | Shows whether time is day or night | |
| IF(AND(HOUR([Time])>6,HOUR([Time])<18),”Day”,”Night”) | Day | |
| AM/PM | Shows whether a time is AM or PM | |
| IF(HOUR([Time]) < 12,”AM”,”PM”) | PM | |
| OTHER | ||
| Modified | Shows whether an item has been modified since creation | |
| IF([Modified] > [Created], “Changed”, “Original”) | Changed | |
| Marks out of ten | Gives general comments on a mark out of ten | |
| CHOOSE(INT([Marks]/3),”Bad”,”Poor”,”Good”,”Great”) | Great | |
| Random String | Chooses a string at random, based on the time (in seconds) | |
| CHOOSE(MOD(TEXT(Created,”s”),2)+1,”String A”,”String B”, “String C”) | String C | |
| TEXT | ||
| TEXT (Value, Format) | Converts Value to a Text value, using Format | 2012|04 |
| TEXT([Created], “yyyy|mm”) | ||
| REPT (Text, Number) | Repeats Text the given Number of times | HelloHelloHello |
| REPT(“Hello”,3) | ||
| FIXED (Num, Dec, NoCommas) | Returns Number with the given number of decimals as text (commas optional) | |
| FIXED(2044.23,1,TRUE) | 2044.23 | |
| FIXED(2044.23,0,FALSE) | 2,044 | |
| LEN (Text) | The length of Text | 4 |
| LEN(“Hola”) | ||
| LEFT (Text, Number) | Return X characters from the left | |
| LEFT(“The Quick Brown Fox”, 5) | The Q | |
| RIGHT (Text, Number) | Return X characters from the right | |
| RIGHT(“The Quick Brown Fox”, 5) | n Fox | |
| MID (Text, Num1, Num2) | Returns Number2 characters from the middle of Text, starting at Number1 | |
| MID(“The Quick Brown Fox”, 4, 15) | Quick Brown | |
| SEARCH (Text1, Text2, Num) | Returns the index of Text1 within Text2,starting the search at index Number | |
| SEARCH(“Banana”, “Banana Banana”, 4) | 8 | |
| LOWER (Text) | Text in lower case | |
| LOWER(“Hello”) | hello | |
| UPPER (Text) | Text in upper case | |
| UPPER(“Hello”) | HELLO | |
| PROPER (Text) | Capitalize first letter of each word | |
| PROPER(“good morning”) | Good Morning | |
| TRIM (Text) | Removes spaces from the start and end | |
| TRIM(” Hello “) | Hello | |
| CLEAN (Text) | Returns Text without non-printable characters added by clipboard or similar | |
| CLEAN(“String1? String2??”) | String1 String2 | |
| REPLACE (T1, N1, N2, T2) | Replaces Number2 characters starting at Number1 from Text1 with Text2 | |
| REPLACE(“Hello”,2,4,”i”) | Hi | |
| CONCATENATE (T1, T2, …) | Combines the string values together into one string | |
| CONCATENATE(“A”,” Fine “,”Morning”) | A Fine Morning | |
| DOLLAR (Number, Decimals) | Converts number to currency text, with the given number of decimals | |
| DOLLAR(11.267,2) | $11.27 | |
| EXACT (Text1, Text2) | Checks if two text values are identical, returns boolean | |
| EXACT(“Hello”,”hello”) | False | |
| MATH | ||
| SUM (Number1, Number2, …) | Returns the total of all Numbers and number-like values | |
| SUM(0, 2, “26?, 100, TRUE) | 128 | |
| MINA (Number1, Number2, …) | Gets the smallest of the numbers, including non-number values | |
| MINA(0, 2, “26?, 100, “MyString”, TRUE) | 0 | |
| MIN (Number1, Number2, …) | Gets the smallest of the numbers, including Text fields containing numbers | |
| MIN(0, 1, “26?, 100) | 0 | |
| MAXA (Number1, Number2, …) | Gets the largest of the numbers, including on-number values | |
| MAXA(0, 2, “26?, 100, “MyString”, TRUE) | 100 | |
| COUNTA (Value1, Value2, …) | Counts all values, including empty text (“”), ignoring empty columns | |
| COUNTA(5, 0,TRUE) | 3 | |
| COUNT (Num1, Num2, …) | Averages the Numbers, ignoring non-Number values | |
| COUNT(5, 0,TRUE) | 2 | |
| AVERAGEA (Num1, Num2, …) | Averages the Numbers, non-Number values are interpreted | |
| AVERAGEA(5,0, TRUE) | 2 | |
| AVERAGE (Num1, Num2, …) | Averages the Numbers, ignoring non-Number values | |
| AVERAGE(10, 0, “”, “0?) | 5 | |
| VALUE (Text) | Converts Text to a Number, Date or Time, according to its format | |
| VALUE(“00:05?) | 00:05 | |
| TRUNC (Number) | Returns Number with decimals removed | |
| TRUNC(14.999999) | 14 | |
| SQRT (Number) | Returns the square root | |
| SQRT(25) | 5 | |
| SIGN (Number) | Returns -1 for negative numbers, 1 for positive, and 0 when 0 | |
| SIGN(-5.2786) | -1 | |
| ROUNDUP (Num1, Num2) | Rounds Number1 to Number2 decimals, always rounding up | |
| ROUNDUP(22.0001, 0) | 23 | |
| ROUNDDOWN (Num1, Num2) | Rounds Number1 to Number2 decimals, always rounding down | |
| ROUNDDOWN(122.492, 1) | 122.4 | |
| ROUND (Number1, Number2) | Rounds Number1 to Number2 decimals | |
| ROUND(221.298, 1) | 221.6 | |
| PI () | Returns Pi to 15 decimal places | |
| PI() | 3.14159265358979 | |
| ODD (Number) | Rounds Number up to the nearest odd number | |
| ODD(1.5) | 3 | |
| MOD (Number1, Number2) | Returns the remainder of Number1 divided by Number2 | |
| MOD(5, 4) | 1 | |
| EVEN (Number) | Rounds Number up to the nearest even number | |
| EVEN(0.5) | 2 | |
| ABS (Number) | Makes a number positive if it is negative | |
| ABS(-1) | 1 | |
| LOGICAL | ||
| AND (Condition1, Condition2) | Returns True if both conditions are True | |
| AND(4>=3,3>2) | True | |
| OR (Condition1, Condition2) | Returns True if either condition is True | |
| OR(4>=3, 3<2) | True | |
| NOT (Condition1) | Returns the opposite to the condition | |
| NOT(1=1) | False | |
| CHOOSE(Num, Val1, Val2, …) | Returns the value corresponding to the number. Up to 29 values can be used. | |
| CHOOSE(2, “A”, “B”, “C”, “D”) | B | |
| IF(Condition, Val1, Val2) | If Conditon is true, return Value1, otherwise return Value2 | |
| IF([Modified] > [Created], “Changed”, Original) | Changed | |
| ERROR & TYPE CHECKING | ||
| ISTEXT (Value) | Returns True if Value is Text | |
| ISTEXT(99) | False | |
| ISNUMBER (Value) | Returns True if Value is a Number, oherwise False | |
| ISNUMBER(99) { | True | |
| ISNONTEXT (Value) | Returns True if Value is not text or is empty, False otherwise | |
| ISNONTEXT(99) | True | |
| ISNA (Value) | Returns True if Value returns error #N/A, otherwise False | |
| ISERR(#N/A) | True | |
| ISLOGICAL (Value) | Returns True if Value returns a logical value (True or False), False otherwise | |
| ISLOGICAL(FALSE) | True | |
| ISERR (Value) | Returns True if Value returns an error (except #N/A), otherwise False | |
| ISERR(#REF!) | True | |
| ISBLANK (Value) | Returns True if Value is empty, otherwise False | |
| IF(ISBLANK([Attendee]) | Needs Attendee | |
| FORBIDDEN COLUMNS | ||
| Lookup columns | Not supported | |
| [ID] | Only works on column addition/update, will not work from then on | |
| [Today] and [Me] | Only available in default columns |
