Calculated columns are the SharePoint equivalent of Excel formulae. They allow you display values that are calculated from the values of one or more other columns. These calculations can be made using standard mathematical operators and SharePoint's built-in functions.

learn more… | top users | synonyms

13
votes
4answers
14k views

How to use Today and Me in Calculated column

I want to use a combination of Today and ME in SharePoint 2013. I am trying it in office 365 it gives me the below error Calculated columns cannot contain volatile functions like Today and Me. Can ...
12
votes
0answers
292 views

Save Calculated Column Formula in Language Localized notation using REST or JSOM?

[multiple blogs/posts out there noted the issue, no solution was ever published] What is required to save a Localized Calculated Column Formula using JSOM/REST? I am injecting the script below into ...
9
votes
2answers
54k views

Using calculated field to retrieve substring of another field

Suppose I have a field that contain the following data: ========================== | Email Address | ========================== | Jack@wonderland.com | | Peter@nobodyland.com | | Jill@...
9
votes
2answers
2k views

How can I display a message on a list item when within X days of a date?

I'm using SharePoint 2010 and I'd like to create a column in Task List that will display a message (not generate an email: just display a field text) such as "URGENT" or "Almost Due" when the date is ...
8
votes
5answers
33k views

How to use calculated field to retrieve the ID of the item in the SharePoint 2007 List?

I have a calculated field in a SharePoint 2007 list. I want this calculated field to include the ID of the item but I could not find the ID column inside the "Insert Column" List box. How should I go ...
7
votes
5answers
20k views

how to show HTML code in column?

how can i force sharepoint2007 (MOSS) to interpret HTML code instead of showing it? I have a column which calculates a yes or no (depending on other columns) within a HTML (i want to show images too)....
7
votes
6answers
30k views

get the month from a date column with the calculated column

I am trying to get the month from a date field with the help of calculated column. However I get this syntax error whenever I want to submit the formula: Error The formula contains a syntax error or ...
7
votes
4answers
46k views

Column calculated value to get the current date time?

I have tried using =TEXT(Today,"mm-dd-yyyy hh:mm:ss") but that only returns the 5/5/55 00:00:00, it won't give the time. How do I get both the date and time with a calculated formula?
7
votes
4answers
1k views

Check if more than 3 hours

I need a calculated column to display "Yes" if the current time is more than 3 hours after the value of another column. This is what I've come up with: =TEXT(Today-[AddTime],"h")>3 But SP ...
6
votes
2answers
26k views

Calculated value for Title field?

I would like to create a calculated value for the mandatory Title field: <username, created by> - DD-MM-YYYY That is I want the title to be filled in with the user name (the user that creates ...
6
votes
4answers
6k views

calculated field - formula for ageing

Again a calculated field question, How can I create a calculated field to find out, what is the age of the entry. In the list I will have a created date field, from that I would like to create a ...
5
votes
3answers
13k views

Creating CASE statement with Calculated Column

I am using SharePoint 2010. I added a new Column and made it a Calculated Column. Now I need to place a CASE statement in my Formula. Something like this... CASE when [Region1] = 'A' and [Region2] =...
5
votes
6answers
24k views

How to use [Created By] in a column?

SP 2007, calendar list. Want to add a column that gets its default value from "Created By" but then allows that to be edited. This is what I tried: Create column Give it a name Leave type set to ...
5
votes
5answers
33k views

How do you Sum a Calculated Column?

I have a calculated column in a list and I would like to get a sum of that column in the view. I can't use datasheet view, and I don't like the idea of a workflow because the user only sees a few ...
5
votes
3answers
11k views

How to find out the number of the week in a calculated field?

How can I calculate the number of the week based on a date in a list? I tried several examples I found on the internet, but none worked correctly? The formula should comply with the ISO standard ...
5
votes
1answer
56 views

How to set calculate column result in another item

I made a list for Event Planning, the columns name are: Row(to change the order) Subject start time duration end time(sum of start and duration) I need to copy each end time to start time ...
5
votes
6answers
13k views

How to resolve full names from a Person or Group column (that contains multiple entries) in a designer workflow

I have a custom list with a custom content type, with a workflow, on SharePoint 2007 as outlined in this other StackExchange question. I'm using a Person or Group typed column to contain a list of ...
4
votes
4answers
10k views

Calculated column with a Date

I am creating a list whereby I want following function: Current Day or Today - 2 days = Output Date i.e. 23/12/2010 - 2 days = 21/12/2010 Is it possible to change the current dates dynamically ...
4
votes
1answer
12k views

Calculated field Date formula

I have a date field, and it has the date in the format - 11/2/2011 11:40 AM How ever my requirement is to get the date in the format - 02 Nov 2011 11:40 AM For that I created a calculated field for ...
4
votes
3answers
1k views

DateTime calculated Column using Formula

I have a column Birthday which contains the date of the user's birthday. I would like to create a calculated column to hold the user's next birthday. For instance, if I wanted to know the birthday in ...
4
votes
4answers
16k views

How to set the Default value of a Date/Time column to next Friday?

I did some poking around in the documentation for Calculated field formulas, but I don't see anything that would help me do this. Does anyone know if it can be done? Can you point me in the right ...
4
votes
1answer
140 views

Calculated column increase date column by one day

I want to create acalculated column that takes a date field and sets the value to the date, increased by one day. How can this be done? For ex: If date field value: 20/10/2015 calculated result will ...
4
votes
1answer
2k views

Calculated Value of “Start Time” in a calendar is not working

I am having a problem with a content type-specific, Reusable workflow. I use it for a calendar. When ever an event is created, I send an email to the approver with Name of the event, start time and ...
4
votes
1answer
4k views

Calculated Hyperlink Column - Lost ID-Field Value Upon Editing

I created a calculated column with the following formula: ="<a href='../X_C03 Staff Members/DispForm.aspx?ID="&ID&"'>View</a>" and included this into a view. The column works ...
4
votes
1answer
13k views

Calculated Column with Nested IF statments

Here is my current formula that works. ="<DIV style='font-weight:bold; font-size:24px; color:"&IF([Project Close A]="",IF(Program="CAPITAL",IF([Project Close P]>=[Current Date],"green","red"...
4
votes
4answers
3k views

Get difference in Minutes for DateTime format column

I have a formula for calculated column which gets the date difference in hh:mm format. =TEXT([COLUMN2]-[COLUMN1],"h:mm") But I want to get the difference in just minutes. I created very complex ...
3
votes
1answer
232 views

How to have a calculate column that compares the difference between the current item and the previously submitted item?

I have a list with the columns Room Number, Temperature, and a column Temperature Change. What I would like, is for Temperature Change to be a calculated like so: When the user fills out a new ...
3
votes
2answers
3k views

Use calculated column to add target“_blank” at the end of a Web field URL

It appears I cannot configure a hyperlink column to open in a new window. I read in this post that you can concatenate a hard coded url with the title field of an item to achieve this. However, that ...
3
votes
3answers
4k views

Sharepoint - Calculated Column showing different colours based on list item age

Marc, I understand you are a busy man, but if you can spare atleast sometime to my query below then I will really appreciate the gesture. Background: We get several requests (items on SharePoint ...
3
votes
6answers
10k views

How to add line breaks in calculated columns without going into data view?

Just wondering how to create a list with line breaks in a calculated column without using data view.
3
votes
1answer
2k views

Unescape HTML from list column

I've got a multiline column in a list that contains HTML. When trying to use the field in SharePoint Designer 2010, it shows it as raw HTML, instead of rendering it. So using this syntax: <xsl:...
3
votes
2answers
4k views

How do I use “Approval Status” in a Calculated Field

I am trying to create a calculated field that will add the text [PEN] to the beginning of the displayed text on my Calendar when the event is still "Pending" according to its "Approval Status". If ...
3
votes
1answer
47 views

calclulate a sum excluding NA results. SP10

I have 6 columns. Each column has a NA, 1 or 0 entered. I need column7 to calculate a score (preferably as a percentage), however NA should not be included in the count. EG 1, 1, 1, 0, NA, 1 =...
3
votes
2answers
129 views

Calculated field based on a field in another list

I would like to create a calculated field which requires a digit. This digit must be between a Max and Min value which are two ListItems in another list. How can I create a formula for this calucated ...
3
votes
2answers
800 views

SharePoint 2013 Find/Replace Calculated Column

Was hoping someone could assist me with regards to SharePoint Calculated column. I have an address column which has an &. We need to find and replace the & with and. The problem is that the &...
3
votes
2answers
61 views

Calculate monthly occurance

I wanted to created a calculated SharePoint column which gives me a sum of the monthly occurrence. For example: During the month of July there were 7 occurrences so I wanted a calculated field to ...
3
votes
2answers
4k views

lookup column in calculated column formula

I have a Lookup column(Discountfirst) which has Number. I also have another Calculated column(Finaldate) with Resulttype DateTime. One more column(DateofReceipt) in Date. My Calculated column formula ...
3
votes
2answers
91 views

Calculated Column - IF true AND true

Using a task list I am trying to show a value if a cell is = to a certain string, AND task is complete. Im currently using this but returning a #VALUE error =AND([Task Name]="Task 2",Completed,"1","...
3
votes
1answer
482 views

Calculated datetime column returns string;# in search index

We have a calculated column, that results in a date. It's output type is set to DateTime - Date only. When we run the search indexer, it shows up in "crawled properties" as a Text-column, and in the ...
3
votes
2answers
2k views

IF statement, Trouble with formula

I have created a simple list where we record time spent on troubleshooting customers. But now I would like to create a simple formula for a Calculated field, but I can't get it to work. We have two ...
3
votes
1answer
68 views

filtering rest api call by calculated column

I am not getting any results when I try to filter by a calculated column/field. The calculated field returns yes/no and it displays YESes and NOs in the view on browser. But when filtered for Yes or ...
3
votes
1answer
29 views

SharePoint Online - Selecting most recent date column between two columns in a list

I have a SharePoint list in Office 365 environment that (among other columns) has two DATE columns, how would I create another calculated column that would have the most recent date from those two ...
3
votes
2answers
2k views

How to create a calculated column based on Workflow Status column

I want to create a calculated column based on Workflow Status column which the system is not allowing. The error is "One or more column references are not allowed, because the columns are defined as a ...
3
votes
1answer
1k views

CAML Query on a Lookup Field that points to a Calculated Field throws Exception

I wrote some CAML Queries to get data from a List that has lookups on another List. All is working fine as long as I do not reference a Lookup Field which points to a Calculated Field. I tried ...
3
votes
2answers
3k views

SharePoint Calculated Field Limitations - Need Work Around

I have the following in a calculated field. I need to add many more IF statements but when I start adding more to the code I begin to get Syntax Error. I need a workaround to make this work. =IF(...
3
votes
2answers
2k views

How to calculate business days excluding Thursdays and Fridays given between two dates?

How to get the business days excluding the weekends (Thursdays and Fridays) in between two given dates? Given dates are two list columns (Start Date & End Date) and business days is a calculated ...
3
votes
0answers
51 views

Calculate Remaining Spent on a budget list

Goal: Trying to calculate remaining spent on a budget list where item expenses are grouped per object code. Every object code has a fixed amount of money against which the item expenses are deducted. ...
3
votes
1answer
206 views

Duplicate filter values in a calculated column

I have a document library with a calculated column. This document library is populated from a windows service. Every time a new document is added, the calculated column has a formula to choose ...
3
votes
3answers
15k views

Auto populate values in a sharepoint list based on another list

I have two Lists in Sharepoint 2010. They are PO master and Invoice Master. The columns in PO Master are PO number PO value Invoiced Amount Balance. The columns in Invoice Master are PO ...
3
votes
0answers
404 views

CAML Contains on a lookup field of a calculated column

I have some issue when using the CAML Contains statement when the field is a lookup field on a calculated column. It basically does not work, the Equal works though. here is the query, nothing ...