About SharePoint Calculated Columns

Posted on by

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.

65 thoughts on “About SharePoint Calculated Columns”

  1. Pingback: EndUserSharePoint.com: Resource for Calculated Columns : End User SharePoint

  2. Pingback: Troubleshooting your “HTML calculated column” « Path to SharePoint

  3. 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.

  4. 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).

  5. 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.

    • 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?

  7. 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?

  8. 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

  11. 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.

  13. 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])’

  15. 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.

  16. 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.

  17. 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

  19. 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

  22. Pingback: SharePoint calendars: color coding, hover effects, etc. « Path to SharePoint

  23. Pingback: Color coding: more examples « Path to SharePoint

  24. 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..

  25. 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..

  26. 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!

  27. 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

  28. Pingback: формулы и SharePoint « Sharepoint .BY

    • 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 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.

  31. 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).

  32. 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.

  33. 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?

  34. 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

  35. 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

  36. 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 😦

  37. 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.

  38. 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

  39. 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.

  40. 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)

  41. 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.

  42. 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

  43. 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.

  44. I have a group of Managers that initiate change requests. I’ve assigned each of my team members a group of these Managers to responsible for, so that it’s clear what change requests they should be managing. I’ve created a “List” to help manage this pipeline.

    I want my team to able to select the Managers abbreviated names from a dropdown list, and have another column automatically enter the assigned team member name in the Assignment field. I have attempted hours of trial and error (all resulting error). And i have had a difficult time articulating this into an affective search query. I would love some help with this.

  45. This is a different one than what I’ve seen everyone else has:
    I’m creating an online work order system and would like to use the [Created] column to generate a unique Work Order Number. I created the WO# column as a calculated column with the following formula:

    =(((Created-INT(Created))*24)/100)+(TEXT(Created,”yy”)&TEXT((Created-DATEVALUE(“1/1/”&TEXT(Created,”yy”))+1),”000″))

    This creates a WO# using the Julian date and time calculation (WO# =YYDDD.TTTTT, or for example, a record created on 11/5/13 at 11:54am generates a WO# of 13,309.11906). This worked well for what I wanted it to do.

    However, when the record is accessed and edited (like a technician entering information on how he addressed the issue), the value shown under WO# becomes #VALUE!. That is, until I edit the column and come back to the list. After that, the WO# value returns to the prior value. Can you (or anyone else viewing) give me some insight as to why this is happening and how I can fix it?

    Is there another way to have the system calculate a unique ID to my specifications (as opposed to the system’s default ID column)?

    Thanks in advance

  46. Is there anyway to have several calculated columns do their calculations in a particular order. I am finding if I am using other calculated columns in another calculated column, if the referenced calulated column has not yet been populated, then I get #Value! but if it has compeleted I get what I am expecting? I need to have values populate in other calculated columns first, before having the another calulated column use them do its calculation.

    • I am surprised by this behavior, I would expect SharePoint to do calculations in the correct order.

      Maybe you did a triangular reference that SharePoint did not catch, with 3 or more calculated columns referencing each other?

      • It appears my calculated columns got confused using my overridden column names in their calculations (can be done when on the create column page, by using a javasciplet that you replace the HTPP address with: javascipt:g_FieldName={};alert(‘ok’); ) . Calculated columns rely soley on the display name of the column, I had forgotten. I had overridden some of the column names, as I was using many content types in the same library and wanted the same name column when displaying views at various custom content folder levels. I have since gone back and reworked my calculated columns to rely on data in two particular, uniquely named, non-calculated columns. After doing so, I then could use my newly calculated columns inside other calculated columns, with no problem (minus any reference to overridden column names). Sorry for the question. It has been a very complex project (58+ content types in one document library) in MOSS.

Comments are closed.