About SharePoint Calculated Columns
Calculated columns – An example
By adding a calculated column to a list or library, you can create a formula that includes data from other columns and performs functions to calculate dates and times, to perform mathematical equations, or to manipulate text. For example, on a tasks list, you can use a column to calculate the number of days it takes to complete each task, based on the Start Date and Date Completed columns.
(Excerpt from the Microsoft Web site)
Let me use this Microsoft example to walk you through the setup of calculated columns.
We first need to create our SharePoint tasks list (Site Actions > Create > Tasks).
It already includes a [Start Date] column, let’s add the [Date Completed] one:
- Select: Settings > Create Column
- Create a column called “Date Completed”, with type set as ”Date and Time”.
- Click OK
- We are now ready to create our first calculated column:
- Select: Settings > Create Column
- Create a column called “Duration”, with type set as ”Calculated (calculation based on other columns)”
- Enter the formula:
= [Date Completed]-[Start Date] - - Click OK
Let’s take a look at the result:
As you can see, the result is incorrect when the [Date Completed] field is empty. To fix this, we’ll add a condition to our formula:
=IF([Date Completed]=0,”",[Date Completed]-[Start Date])
Now, the duration will only be displayed after the user enters a value for “Date Completed”:
Web references
Many articles have been written on calculated columns, so instead of trying to reinvent the wheel I am mainly going to point to some excellent references available on the Web.
Dessie Lunsford has recently published a well documented series to guide end users through the subtleties of calculated columns:
http://www.endusersharepoint.com/?cat=399
For a comprehensive list of available functions, see the Microsoft help:
- Directly from your SharePoint site: open the SharePoint help by clicking on the
button on the top right of your SharePoint page. Then search for “formulas”, or follow the path:
SharePoint Server 2007 Help and How-to > Business intelligence > Business data in sites, lists, and libraries > Formulas and functions - On the Microsoft Web site:
Data calculations
Formulas and functions
Examples of common formulas
You can also use Excel as a reference, as Dessie points out in his series, but be aware that not all Excel functions work in SharePoint.
Some limitations of calculated columns
- You cannot reference a value in a row other than the current row (as opposed to Excel for example).
- You cannot reference a value in another list or library.
- You cannot reference lookup fields in a formula.
- You cannot reference the ID of a row for a newly inserted row. The ID does not yet exist when the calculation is performed.
I can’t seem to find any documentation on what you can and cannot use in calculated columns.
For example, I want to create a Calculated Site Column which is a choice field and the choices are the names of sibling sites. I can manually enter those site names and maintain that (although not ideal) in the site column gallery – the real kicker is I want the default selection to be the current site name. The default allows for a calculation or a choice.
I have been trying to find some reference to the site that I could use (for example my calculation would look like “=[Site Name]“) but have not found anything that allows such a reference. Wondering if this is a lost cause or not. Any pointers to documentation or ideas on making this work would be appreciated.
trock, first understand that “calculated column” and default calculation for a column are two different things. You are talking about the latter if I understand correctly.
I haven’t found any documentation either, and the possibilities are very limited. As far as I know, the only parameters you can use for a default calculation are [Today] (the date when you create the item) and [Me] (the username of the user creating the item).
Hi!
I have a problem with Calculated columns. Is there a limitation on the length of the formula. I have created a formula that’s 1100 characters long and syntax i correct. If I reduce the formula to less than 1024 chars it works.
Hans: yes, there is a size limit. Try to simplify your formula (for example use CHOOSE instead of nested IFs), or split it in two.
I have a question. I would like to create a column that gets a new id based on events created in a calendar. The calendar events i suppose are getting this new automatic id. Is there a way to reference the id?
I have 25 content types, and for each of them I want to read information from a column(different for each content type). Is there anyway to enumerate them (the content types) so that i can use the CHOOSE-function?
Hi Christoper,
First i like to thank you for posting such wonderful blogs.
I have a scenario for a custom button functionality for a sharepoint list.
At this stage i am able to load a button in the view list(using custom field type and adding CDATA in the render pattern)
i am stuck here..
1)i need to show this button column”MarkComplete” if the “CompletedDate” column is blank.
2) When i click the button in the column on the click event i need to update the “CompleteDate” column
2)If the “CompletedDate” is not blank the Button Column “MarkComplete” should be invisible.
Please guide me how to proceed any sample code snippet also will be truly grateful..
Thanks,
Subhan
Hans: so I was correct when assuming you were using nested IFs?
I don’t know about your precise case. I have published some examples using CHOOSE here:
http://pathtosharepoint.wordpress.com/2008/12/09/color-coding-more-examples/
This may give you some ideas. As you can see, you need to think outside of the box to make the best of it.
Another example here:
http://www.endusersharepoint.com/?p=1047
The author was using two calculated columns and a bunch of “IF”; I commented that it could easily be simplified with two CHOOSE functions.
I have a list with following columns:
Company Paid: Yes/No
Charged to Client: Yes/No
Amount: $
Now, I want to create an additional calculated column that do the following:
If charged to client = Yes, then copy value from Amount field.
I thought I could use Excel functions, so I used If([Charged to Client]=”yes,[Amount]). This would work in excel but not in SharePoint.
Any help is appreciated.
‘=IF([Charged to Client] = “Yes”,[Amount],”")’
if true,this statement will give the amount, if false it will be blank, if you have some value for false place it between the “” at the end.
Excell formulas are very close to sharepoint. here is a good reference
http://office.microsoft.com/en-us/sharepointtechnology/CH100650061033.aspx
another tip for the above, I have found depending on the field type whether it is a choice or Yes/No, I usually like to write my statement like this
‘=IF([Charged to Client] “Yes”,”0″,[Amount])’
lost the compasison operator ‘< >’ not equals
Hi Christophe,
I wanted to clarify one thing. In the Limitations section above, it states you cannot reference lookup fields in a formula. I have a Custom List that I created based upon a child content type (let’s call it “Test” for these purposes). The parent content type is Item. In this content type, I have several lookup columns based on other lists that I have created i.e. “Program Status, Program Type, Program Category” and so on. I want to be able to create a condition so that each time any of these columns are modified in the past 7 days, the text will change to red. Once that 7 days has lapsed, the text will display normally (as black). So, can these columns not be used in a calculated column since they are essentially lookup columns?
Thanks.
beargal: from your description, I’d say that you can’t.
A workaround would be to use a workflow that will copy the content of the calculated column in a standard text column.
In a list I have start date and estimated hour and Let’s say estimated hour are 20, to convert it # of days I divide it with 8.
Now question is
1) how I can add the # of day and hour to start date.
2) if I am using the calculated date i.e End date (start date + estimated hour) column in calendar view, I am getting error.
Could you put some light on it.
Thanks
Mas
Mas:
1) I think you answered yourself: start date + estimated hours/8
2) This is a known issue with calendars:
http://pathtosharepoint.wordpress.com/2008/11/13/issues-with-calendars/
Hello Christophe,
I tryied the following formula:
“[Today]-8,”blue”,”black”)&”;’>”&[Work Item ID]&”"
SharePoint’s response was: Calculated columns cannot contain volatile functions like Today and Me.
The idea is to have the calculated column to display the number in [Work Item ID] in blue if the row has been changed within the last seven days.
Is there a way to do this?
Thanks for the help,
Christophe
Well, SharePoint is correct
Do a search for “Today trick” on my blog:
http://pathtosharepoint.wordpress.com/?s=today+trick
Thanks, the Today and Me tricks were a great help.
Getting addicted to your site!
Hi, its good..I have two fields..’weekly hours’ and ‘total hours’
now I want to have formula for ‘total hours’ = ‘total hours’ + ‘weekly hours’..
so that whenever I add some hours in weekly hours it get added to ‘total hours’.. currently I am not able to get this formula..
pls hel..
I don’t think you can do that with calculated columns. See if a workflow could help in your case.
Hi, I have a problem with calculated a columm – too many if statements. Even after readying your examples can’t work out if I can use CHOOSE.
I have the following 15 combinations in total. the IF statement are:
IF(combine=”veryHighveryHigh”,”1″ etc.
Is it possible for me to use CHOOSE?
Thank you for any help.
Michelle
Combine Rank No.
VeryHighVeryHigh 1
HighVeryHigh 1
MediumVeryHigh 2
MediumHigh 2
MediumMedium 3
LowMedium 3
etc..
Michelle, here is a similar discussion on the STP forum, see if it helps:
http://www.endusersharepoint.com/STP/topic/calculated-column-using-choose-statement-with-9-nested-levels#post-6855
Hello,
I was wondering if anyone knew how to create a calculated formula that will interrogate the Date field, and the calculated column will display the Month in Text format?
ie. Date Column = 13/10/2009
then the Calculated Column would = October
?? Any ideas?
Thanks very much!
Try ‘=TEXT(Date-Field,” mmmm”‘
Can anyone tell me what the column max is for amount of calculated columns in a SharePoint 2007 list? I have seen the number 8 for SharePoint 2003 but can’t find info on the next version up.
Thanks,
A
Hi,
Is there a way to calculate number of documents in a folder in sharepoint?
You cannot use calculated columns for this. Calculated columns are only aware of the item level, not the folder or list level.
Counting the number of items could be done in a Data View Web Part, using SharePoint Designer.
Can we just count the rows? since each document is arranged in a row.
Thank you for the calculated date value with IF condition it worked for me and saved my time.
Hi – can you use calculated column to call a javascript function inside an a href tag? I’m having difficulties with the single and double quote differences causing an error. This html in the calc column won’t work for instance.
=CONCATENATE(““,”View Comments”,”“)
but this html will, as a regular link, not in the calculated column. The main difference being the double quote being used for the href tag instead of single.
View Comments
any ideas to get this to work in calc column?
Double quotes are reserved characters in formulas. To get around this issues, you have two options: single quotes (they are also accepted in html/Javascript), and double double quotes (SharePoint will interpret it as double quotes).
Love your solutions, Christophe! A question for the date difference formula: I’d like to know the number of workdays between a start_date and an end_date. However, NETWORKDAYS(start_date, end_date) does not seem to work in Sharepoint 2007. I also tried creating a formula using your advice here
=IF([end_date]=0,”", NETWORKDAYS([start_date],[end_date]))
Still nothing –
Any thoughts on pulling the weekdays difference between two dates in a Sharepoint calculated column?
Andrew, search the Web and you should find a couple ways to do it. NETWORKDAYS is not accepted in SharePoint.
I’ll also publish my own version in a future post.
This is such a useful site…a quick question about ID in calculated columns…I used your concatenate formula on another page to combine the URL for items in the list with [ID] so that users could link to the item itself (as the name has been concatenated with another field and is no longer a link). It seems to work and show the ID number as a link that works…but as soon as the item is updated, the ID number no longer appears and I need to go into the Site Libraries and Lists to open and save the column again so it apears again…any ideas on how to not have to do this (or smarter ways for me to have a link to the item?
You cannot use the ID field in calculations (see last paragraph of the post). A workaround is to use a workflow to duplicate the ID field.
I am simply trying to create a calculated column that determines if a text column is = to ‘Compliant’ if it is = to compliant then count that entry for each item in that column. This would produce a count (total) for all items in the column = to Compliant.
Sadly I cannot determine how to do it. I hope you can help.
Thank you.
More Info:
A column named Assessement has three entries; 1. Compliant, 2. Non-Compliant and 3. N/A
I have a calculation column that I am trying to complete that does a count of all Compliant entries and it is called #C.
Thanks
Is it possible to have “month to date” type calculations in the calculated column? You have mentioned that its scope is item level, so I am not even sure this is doable since the values from previous items need to be added to the next item column value. Appreciate any insight on this. Thanks
How about if I want to do something like “If ‘Status’ = ‘Done!’ then ‘End Dte’ = [Now]?
I want the “End Dte” date field to be automatically updated when someone checks the “Done!” value from the “Status” multiple choice list. So, I am trying to enter a calculated value for the “End Dte” field, thus far without any success
A calculated column is stateless and won’t let you do that. You’ll need to do it in the form itself or use a workflow.
Can’t seem to find this anywhere, but can information be extracted from a column. Specifically, can I extract JUST the domain from the e-mail column? I want to sort the user list by domain name based on the [Email] column. So I can mass message or delete a whole group based on that rather than having to look through the whole user list.
I think you need to first find the “@” in the field, then only keep the part of the string after it. Check out the SP help on functions and formulas for more details.
For the difference between two dates, getting just weekdays, here is what I use. Not perfect, but it’s really darn close.
=[end date]-[start date]-INT(([end date]-[start date])/7+IF(WEEKDAY([end date])-WEEKDAY([start date])<0,1,0))*2
Some more suggestions can be found in this discussion: http://lnkd.in/aDsy3x
I am using SharePoint 2007 without the Designer function.
I have two lists and would like to prepopulate 1 Column in List A with values in List B based on what is entered in the field in list A. I hope I am making sense?
There is a date column in List A where the user has to enter a date. There is also a period column. In list B there is also a date column which lists all dates together with a matching period which is in another column.
So if user enters a date in list A, I would like the calculated formula to correspond with the period column in list B and automatically populate the period. It should work much like the Vlookup function in Excel. I have heard this is possible in SharePoint 2010 but unfortunately I only have 2007 and as I stated I do not have the SharePoint Designer functionality.
Please help!
In SP 2010, you have the possibility to include related fields. In SP 2007 you’ll need to do the calculation directly in the list, or rely on a workflow that will do the “vlookup” for you (that would require SharePoint Designer). Another more complicated option would be to include a script in the form itself that will do the lookup before you save the item.
Christophe,
Is there way to use something like this where you could 2 Lists. Projects and Project Tasks. Project Tasks would have a lookup column for each project and the Project ID filed from the Projects list.
For each project I would like to calculate the due date of the project based on the maximum value Due date of a project task. In other words I would like my due date to change in my project list based on the furthest out due date for any task in my Project task list.
Is this possible? Does it have to be done with a workflow instead? Could you tell mea way to accomplish this?
It can’t be done with calculated columns alone (a calculated column can only see the current item).
I think such a solution is not easy to build. Certainly a workflow could calculate the maximum due date, but how about updates? (for example if you delete the task that had the maximum due date)
Is there a way to take a standard list and convert it to a calendar view? We have a series of calculated columns and need that to reflect that information in a calendar to feed as a webpart for another webpage. Everytime I try to do it, I get a runtime error message.
Is there any way that you can use this to render a button? I’ve been trying a few different options, but can’t get it to work
Howdy! Someone in my Myspace group shared this site with
us so I came to give it a look. I’m definitely enjoying the information. I’m book-marking and will be tweeting this to my followers!
Fantastic blog and excellent design and style.
I am using several of your tools. Thank you!
I want to change the color of a date if it is <= today.
Can you assist?
You cannot do this directly with calculated columns. For more information, search my blog for “Today trick” and “countdown”.