Calculated Column in SharePoint
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.
- Check if a number is greater than or less than another number
Use the IF function to perform this comparison.
Col1 | Col2 | Formula | Description (possible result) |
15000 | 9000 | =[Col1]>[Col2] | Is Col1 greater than Col2? (Yes) |
15000 | 9000 | =IF([Col1]<=[Col2], "OK", "Not OK") | Is Col1 less than or equal to Col2? (Not OK) |
- Return a logical value after comparing Col contents
For a result that is a logical value (Yes or No), use the AND, OR, and NOT functions.
Col1 | Col2 | Col3 | Formula | Description |
15 | 9 | 8 | =AND([Col1]>[Col2], [Col1]<[Col3]) | Is 15 greater than 9 and less than 8? (No) |
15 | 9 | 8 | =OR([Col1]>[Col2], [Col1]<[Col3]) | Is 15 greater than 9 or less than 8? (Yes) |
15 | 9 | 8 | =NOT([Col1]+[Col2]=24) | Is 15 plus 9 not equal to 24? (No) |
For a result that is another calculation, or any other value other than Yes or No, use the IF, AND, and OR functions.
Col1 | Col2 | Col3 | Formula | Description (possible result) |
15 | 9 | 8 | =IF([Col1]=15, "OK", "Not OK") | If the value in Col1 equals 15, then return "OK". (OK) |
15 | 9 | 8 | =IF(AND([Col1]>[Col2], [Col1]<[Col3]), "OK", "Not OK") | If 15 is greater than 9 and less than 8, then return "OK". (Not OK) |
15 | 9 | 8 | =IF(OR([Col1]>[Col2], [Col1]<[Col3]), "OK", "Not OK") | If 15 is greater than 9 or less than 8, then return "OK". (OK) |
- Display zeroes as blanks or dashes
To display a zero, perform a simple calculation. To display a blank or a dash, use the IF function.
Col1 | Col2 | Formula | Description (possible result) |
10 | 10 | =[Col1]-[Col2] | Second number subtracted from the first (0) |
15 | 9 | =IF([Col1]-[Col2],"-",[Col1]-[Col2]) | Returns a dash when the value is zero (-) |
- Hide error values in Cols
To display a dash, #N/A, or NA in place of an error value, use the ISERROR function.
Col1 | Col2 | Formula | Description (possible result) |
10 | 0 | =[Col1]/[Col2] | Results in an error (#DIV/0) |
10 | 0 | =IF(ISERROR([Col1]/[Col2]),"NA",[Col1]/[Col2]) | Returns NA when the value is an error |
10 | 0 | =IF(ISERROR([Col1]/[Col2]),"-",[Col1]/[Col2]) | Returns a dash when the value is an error |
You can use the following formulas to perform calculations that are based on dates and times, such as adding a number of days, months, or years to a date, calculating the difference between two dates, and converting time to a decimal value.
- Add dates
To add a number of days to a date, use the addition (+) operator.
Note: When you manipulate dates, the return type of the calculated Col must be set to Date& Time.
Col1 | Col2 | Formula | Description (result) |
6/9/2007 | 3 | =[Col1]+[Col2] | Adds 3 days to 6/9/2007 (6/12/2007) |
12/10/2008 | 54 | =[Col1]+[Col2] | Adds 54 days to 12/10/2008 (2/2/2009) |
To add a number of months to a date, use the DATE, YEAR, MONTH, and DAY functions.
Col1 | Col2 | Formula | Description (result) |
6/9/2007 | 3 | =DATE(YEAR([Col1]),MONTH([Col1])+[Col2],DAY([Col1])) | Adds 3 months to 6/9/2007 (9/9/2007) |
12/10/2008 | 25 | =DATE(YEAR([Col1]),MONTH([Col1])+[Col2],DAY([Col1])) | Adds 25 months to 12/10/2008 (1/10/2011) |
To add a number of years to a date, use the DATE, YEAR, MONTH, and DAY functions.
Col1 | Col2 | Formula | Description (result) |
6/9/2007 | 3 | =DATE(YEAR([Col1])+[Col2],MONTH([Col1]),DAY([Col1])) | Adds 3 years to 6/9/2007 (6/9/2010) |
12/10/2008 | 25 | =DATE(YEAR([Col1])+[Col2],MONTH([Col1]),DAY([Col1])) | Adds 25 years to 12/10/2008 (12/10/2033) |
To add a combination of days, months, and years to a date, use the DATE, YEAR, MONTH, and DAY functions.
Col1 | Formula | Description (result) |
6/9/2007 | =DATE(YEAR([Col1])+3,MONTH([Col1])+1,DAY([Col1])+5) | Adds 3 years, 1 month, and 5 days to 6/9/2007 (7/14/2010) |
12/10/2008 | =DATE(YEAR([Col1])+1,MONTH([Col1])+7,DAY([Col1])+5) | Adds 1 year, 7 months, and 5 days to 12/10/2008 (7/15/2010) |
- Calculate the difference between two dates
Use the DATEDIF function to perform this calculation.
Col1 | Col2 | Formula | Description (result) |
01-Jan-1995 | 15-Jun-1999 | =DATEDIF([Col1], [Col2],"d") | Returns the number of days between the two dates (1626) |
01-Jan-1995 | 15-Jun-1999 | =DATEDIF([Col1], [Col2],"ym") | Returns the number of months between the dates, ignoring the year part (5) |
01-Jan-1995 | 15-Jun-1999 | =DATEDIF([Col1], [Col2],"yd") | Returns the number of days between the dates, ignoring the year part (165) |
- Calculate the difference between two times
To present the result in the standard time format (hours:minutes:seconds), use the subtraction operator (-) and the TEXT function. For this method to work, hours must not exceed 24, and minutes and seconds must not exceed 60.
Col1 | Col2 | Formula | Description (result) |
06/09/2007 10:35 AM | 06/09/2007 3:30 PM | =TEXT([Col2]-[Col1],"h") | Hours between two times (4) |
06/09/2007 10:35 AM | 06/09/2007 3:30 PM | =TEXT([Col2]-[Col1],"h:mm") | Hours and minutes between two times (4:55) |
06/09/2007 10:35 AM | 06/09/2007 3:30 PM | =TEXT([Col2]-[Col1],"h:mm:ss") | Hours, minutes, and seconds between two times (4:55:00) |
To present the result in a total that is based on one time unit, use the INT function, or HOUR, MINUTE, or SECOND function.
Col1 | Col2 | Formula | Description (result) |
06/09/2007 10:35 AM | 06/10/2007 3:30 PM | =INT(([Col2]-[Col1])*24) | Total hours between two times (28) |
06/09/2007 10:35 AM | 06/10/2007 3:30 PM | =INT(([Col2]-[Col1])*1440) | Total minutes between two times (1735) |
06/09/2007 10:35 AM | 06/10/2007 3:30 PM | =INT(([Col2]-[Col1])*86400) | Total seconds between two times (104100) |
06/09/2007 10:35 AM | 06/10/2007 3:30 PM | =HOUR([Col2]-[Col1]) | Hours between two times, when the difference does not exceed 24 (4) |
06/09/2007 10:35 AM | 06/10/2007 3:30 PM | =MINUTE([Col2]-[Col1]) | Minutes between two times, when the difference does not exceed 60 (55) |
06/09/2007 10:35 AM | 06/10/2007 3:30 PM | =SECOND([Col2]-[Col1]) | Seconds between two times, when the difference does not exceed 60 (0) |
- Convert times
To convert hours from the standard time format to a decimal number, use the INT function.
Col1 | Formula | Description (result) |
10:35 AM | =([Col1]-INT([Col1]))*24 | Number of hours since 12:00 AM (10.583333) |
12:15 PM | =([Col1]-INT([Col1]))*24 | Number of hours since 12:00 AM (12.25) |
To convert hours from a decimal number to the standard time format (hours:minutes:seconds), use the division operator and the TEXT function.
Col1 | Formula | Description (result) |
23:58 | =TEXT(Col1/24, "hh:mm:ss") | Hours, minutes, and seconds since 12:00 AM (00:59:55) |
2:06 | =TEXT(Col1/24, "h:mm") | Hours and minutes since 12:00 AM (0:05) |
- Insert Julian dates
A Julian date refers to a date format that is a combination of the current year and the number of days since the beginning of the year. For example, January 1, 2007, is represented as 2007001 and December 31, 2007, is represented as 2007365. This format is not based on the Julian calendar.
To convert a date to a Julian date, use the TEXT and DATEVALUE functions.
Col1 | Formula | Description (result) |
6/23/2007 | =TEXT([Col1],"yy")&TEXT(([Col1]-DATEVALUE("1/1/"& TEXT([Col1],"yy"))+1),"000") | Date in Julian format, with a two-digit year (07174) |
6/23/2007 | =TEXT([Col1],"yyyy")&TEXT(([Col1]-DATEVALUE("1/1/"&TEXT([Col1],"yy"))+1),"000") | Date in Julian format, with a four-digit year (2007174) |
To convert a date to a Julian date that is used in astronomy, use the constant 2415018.50. This formula only works for dates after 3/1/1901, and if you are using the 1900 date system.
Col1 | Formula | Description (result) |
6/23/2007 | =[Col1]+2415018.50 | Date in Julian format, used in astronomy (2454274.50) |
- 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.
Col1 | Formula | Description (possible result) |
19-Feb-2007 | =TEXT(WEEKDAY([Col1]), "dddd") | Calculates the day of the week for the date and returns the full name of the day (Monday) |
3-Jan-2008 | =TEXT(WEEKDAY([Col1]), "ddd") | Calculates the day of the week for the date and returns the abbreviated name of the day (Thu) |
Mathematical formulas
You can use the following formulas to perform a variety of mathematical calculations, such as adding, subtracting, multiplying, and dividing numbers, calculating the average or median of numbers, rounding a number, and counting values.
- Add numbers
To add numbers in two or more Cols in a row, use the addition operator (+) or the SUM function.
Col1 | Col2 | Col3 | Formula | Description (result) |
6 | 5 | 4 | =[Col1]+[Col2]+[Col3] | Adds the values in the first three Cols (15) |
6 | 5 | 4 | =SUM([Col1],[Col2],[Col3]) | Adds the values in the first three Cols (15) |
6 | 5 | 4 | =SUM(IF([Col1]>[Col2], [Col1]-[Col2], 10), [Col3]) | If Col1 is greater than Col2, adds the difference and Col3. Else add 10 and Col3 (5) |
- Subtract numbers
To subtract numbers in two or more Cols in a row, use the subtraction operator (-) or the SUM function with negative numbers.
Col1 | Col2 | Col3 | Formula | Description (result) |
15000 | 9000 | -8000 | =[Col1]-[Col2] | Subtracts 9000 from 15000 (6000) |
15000 | 9000 | -8000 | =SUM([Col1], [Col2], [Col3]) | Adds numbers in the first three Cols, including negative values (16000) |
- Calculate the difference between two numbers as a percentage
Use the subtraction (-) and division (/) operators and the ABS function.
Col1 | Col2 | Formula | Description (result) |
2342 | 2500 | =([Col2]-[Col1])/ABS([Col1]) | Percentage change (6.75% or 0.06746) |
- Multiply numbers
To multiply numbers in two or more Cols in a row, use the multiplication operator (*) or the PRODUCT function.
Col1 | Col2 | Formula | Description (result) |
5 | 2 | =[Col1]*[Col2] | Multiplies the numbers in the first two Cols (10) |
5 | 2 | =PRODUCT([Col1], [Col2]) | Multiplies the numbers in the first two Cols (10) |
5 | 2 | =PRODUCT([Col1],[Col2],2) | Multiplies the numbers in the first two Cols and the number 2 (20) |
- Divide numbers
To divide numbers in two or more Cols in a row, use the division operator (/).
Col1 | Col2 | Formula | Description (result) |
15000 | 12 | =[Col1]/[Col2] | Divides 15000 by 12 (1250) |
15000 | 12 | =([Col1]+10000)/[Col2] | Adds 15000 and 10000, and then divides the total by 12 (2083) |
- Calculate the average of numbers
The average is also called the mean. To calculate the average of numbers in two or more Cols in a row, use the AVERAGE function.
Col1 | Col2 | Col3 | Formula | Description (result) |
6 | 5 | 4 | =AVERAGE([Col1], [Col2],[Col3]) | Average of the numbers in the first three Cols (5) |
6 | 5 | 4 | =AVERAGE(IF([Col1]>[Col2], [Col1]-[Col2], 10), [Col3]) | If Col1 is greater than Col2, calculate the average of the difference and Col3. Else calculate the average of the value 10 and Col3 (2.5) |
- Calculate the median of numbers
The median is the value at the center of an ordered range of numbers. Use the MEDIAN function to calculate the median of a group of numbers.
A | B | C | D | E | F | Formula | Description (result) |
10 | 7 | 9 | 27 | 0 | 4 | =MEDIAN(A, B, C, D, E, F) | Median of numbers in the first 6 Cols (8) |
- Calculate the smallest or largest number in a range
To calculate the smallest or largest number in two or more Cols in a row, use the MIN and MAX functions.
Col1 | Col2 | Col3 | Formula | Description (result) |
10 | 7 | 9 | =MIN([Col1], [Col2], [Col3]) | Smallest number (7) |
10 | 7 | 9 | =MAX([Col1], [Col2], [Col3]) | Largest number (10) |
- Count values
To count numeric values, use the COUNT function.
Col1 | Col2 | Col3 | Formula | Description (result) |
Apple | 12/12/2007 | =COUNT([Col1], [Col2], [Col3]) | Counts the number of Cols that contain numeric values. Excludes date and time, text, and null values (0) | |
$12 | #DIV/0! | 1.01 | =COUNT([Col1], [Col2], [Col3]) | Counts the number of Cols that contain numeric values, but excludes error and logical values (2) |
- Increase or decrease a number by a percentage
Use the percent (%) operator to perform this calculation.
Col1 | Col2 | Formula | Description (result) |
23 | 3% | =[Col1]*(1+5%) | Increases number in Col1 by 5% (24.15) |
23 | 3% | =[Col1]*(1+[Col2]) | Increases number in Col1 by the percent value in Col2: 3% (23.69) |
23 | 3% | =[Col1]*(1-[Col2]) | Decreases number in Col1 by the percent value in Col2: 3% (22.31) |
- Raise a number to a power
Use the exponentiation operator (^) or the POWER function to perform this calculation.
Col1 | Col2 | Formula | Description (result) |
5 | 2 | =[Col1]^[Col2] | Calculates five squared (25) |
5 | 3 | =POWER([Col1], [Col2]) | Calculates five cubed (125) |
- Round a number
To round up a number, use the ROUNDUP, ODD, or EVEN function.
Col1 | Formula | Description (result) |
20.3 | =ROUNDUP([Col1],0) | Rounds 20.3 up to the nearest whole number (21) |
-5.9 | =ROUNDUP([Col1],0) | Rounds -5.9 up to the nearest whole number (-5) |
12.5493 | =ROUNDUP([Col1],2) | Rounds 12.5493 up to the nearest hundredth, two decimal places (12.55) |
20.3 | =EVEN([Col1]) | Rounds 20.3 up to the nearest even number (22) |
20.3 | =ODD([Col1]) | Rounds 20.3 up to the nearest odd number (21) |
To round down a number, use the ROUNDDOWN function.
Col1 | Formula | Description (result) |
20.3 | =ROUNDDOWN([Col1],0) | Rounds 20.3 down to the nearest whole number (20) |
-5.9 | =ROUNDDOWN([Col1],0) | Rounds -5.9 down to the nearest whole number (-6) |
12.5493 | =ROUNDDOWN([Col1],2) | Rounds 12.5493 down to the nearest hundredth, two decimal places (12.54) |
To round a number to the nearest number or fraction, use the ROUND function.
Col1 | Formula | Description (result) |
20.3 | =ROUND([Col1],0) | Rounds 20.3 down, because the fraction part is less than .5 (20) |
5.9 | =ROUND([Col1],0) | Rounds 5.9 up, because the fraction part is greater than .5 (6) |
-5.9 | =ROUND([Col1],0) | Rounds -5.9 down, because the fraction part is less than -.5 (-6) |
1.25 | =ROUND([Col1], 1) | Rounds the number to the nearest tenth (one decimal place). Because the portion to be rounded is 0.05 or greater, the number is rounded up (result: 1.3) |
30.452 | =ROUND([Col1], 2) | Rounds the number to the nearest hundredth (two decimal places). Because the portion to be rounded, 0.002, is less than 0.005, the number is rounded down (result: 30.45) |
To round a number to the significant digit above 0, use the ROUND, ROUNDUP, ROUNDDOWN, INT, and LEN functions.
Col1 | Formula | Description (result) |
5492820 | =ROUND([Col1],3-LEN(INT([Col1]))) | Rounds the number to 3 significant digits (5490000) |
22230 | =ROUNDDOWN([Col1],3-LEN(INT([Col1]))) | Rounds the bottom number down to 3 significant digits (22200) |
5492820 | =ROUNDUP([Col1], 5-LEN(INT([Col1]))) | Rounds the top number up to 5 significant digits (5492900) |
You can use the following formulas to manipulate text, such as combining or concatenating the values from multiple Cols, comparing the contents of Cols, removing characters or spaces, and repeating characters.
- Change the case of text
To change the case of text, use the UPPER, LOWER, or PROPER function.
Col1 | Formula | Description (result) |
nina Vietzen | =UPPER([Col1]) | Changes text to uppercase (NINA VIETZEN) |
nina Vietzen | =LOWER([Col1]) | Changes text to lowercase (nina vietzen) |
nina Vietzen | =PROPER([Col1]) | Changes text to title case (Nina Vietzen) |
- Combine first and last names
To combine first and last names, use the ampersand operator (&) or the CONCATENATE function.
Col1 | Col2 | Formula | Description (result) |
Carlos | Carvallo | =[Col1]&[Col2] | Combines the two strings (CarlosCarvallo) |
Carlos | Carvallo | =[Col1]&" "&[Col2] | Combines the two strings, separated by a space |
Carlos | Carvallo | =[Col2]&", "&[Col1] | Combines the two strings, separated by a comma and a space (Carvallo, Carlos) |
Carlos | Carvallo | =CONCATENATE([Col2], ",", [Col1]) | Combines the two strings, separated by a comma |
- Combine text and numbers from different Cols
To combine text and numbers, use the CONCATENATE function, the ampersand operator (&), or the TEXT function and the ampersand operator.
Col1 | Col2 | Formula | Description (result) |
Yang | 28 | =[Col1]&" sold "&[Col2]&" units." | Combines contents above into a phrase (Yang sold 28 units.) |
Dubois | 40% | =[Col1]&" sold "&TEXT([Col2],"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 Col2 instead of the underlying value, which is .4. |
Yang | 28 | =CONCATENATE([Col1]," sold ",[Col2]," units.") | Combines contents above into a phrase (Yang sold 28 units.) |
- Combine text with a date or time
To combine text with a date or time, use the TEXT function and the ampersand operator (&).
Col1 | Col2 | Formula | Description (result) |
Billing Date | 5-Jun-2007 | ="Statement date: "&TEXT([Col2], "d-mmm-yyyy") | Combines text with a date (Statement date: 5-Jun-2007) |
Billing Date | 5-Jun-2007 | =[Col1]&" "&TEXT([Col2], "mmm-dd-yyyy") | Combines text and date from different Cols into one Col (Billing Date Jun-05-2007) |
- Compare Col contents
To compare one Col to another Col or a list of values, use the EXACT and OR functions.
Col1 | Col2 | Formula | Description (possible result) |
BD122 | BD123 | =EXACT([Col1],[Col2]) | Compares contents of first two Cols (No) |
BD122 | BD123 | =EXACT([Col1], "BD122") | Compares contents of Col1 and the string "BD122" (Yes) |
- Check if a Col value or a part of it matches specific text
To check if a Col value or a part of it matches specific text, use the IF, FIND, SEARCH, and ISNUMBER functions.
Col1 | Formula | Description (possible result) |
Vietzen | =IF([Col1]="Vietzen", "OK", "Not OK") | Checks to see if Col1 is Vietzen (OK) |
Vietzen | =IF(ISNUMBER(FIND("v",[Col1])), "OK", "Not OK") | Checks to see if Col1 contains the letter v(OK) |
BD123 | =ISNUMBER(FIND("BD",[Col1])) | Checks to see if Col1 contains BD (Yes) |
- Count nonblank Cols
To count nonblank Cols, use the COUNTA function.
Col1 | Col2 | Col3 | Formula | Description (result) |
Sales | 19 | =COUNTA([Col1], [Col2]) | Counts the number of nonblank Cols (2) | |
Sales | 19 | =COUNTA([Col1], [Col2], [Col3]) | Counts the number of nonblank Cols (2) |
- Remove characters from text
To remove characters from text, use the LEN, LEFT, and RIGHT functions.
Col1 | Formula | Description (result) |
Vitamin A | =LEFT([Col1],LEN([Col1])-2) | Returns 7 (9-2) characters, starting from left (Vitamin) |
Vitamin B1 | =RIGHT([Col1], LEN([Col1])-8) | Returns 2 (10-8) characters, starting from right (B1) |
- Remove spaces from the beginning and end of a Col
To remove spaces from a Col, use the TRIM function.
Col1 | Formula | Description (result) |
Hello there! | =TRIM([Col1]) | Removes the spaces from the beginning and end (Hello there!) |
- Repeat a character in a Col
To repeat a character in a Col, use the REPT function.
Formula | Description (result) |
=REPT(".",3) | Repeats a period 3 times (...) |
=REPT("-",10) | Repeats a dash 10 times (----------) |
Remainder After 3 working days
=IF(WEEKDAY(Modified)>3,Modified+5,IF(WEEKDAY(Modified)>1,Modified+3,Modified+4))
Remainder After 4 working days
=IF(WEEKDAY(Modified)>2,Modified+6,IF(WEEKDAY(Modified)<2,Modified+5,Modified+4))
