Stack Exchange Network

Stack Exchange network consists of 175 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

Questions tagged [calculated-column]

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.

39
votes
7answers
91k 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 ...
41
votes
16answers
22k views

June 13th 2017 Microsoft blocked handling HTML markup in SharePoint calculated fields - how to get the same functionality back

On June 13th 2017 all SharePoint calculated columns with HTML markup stopped working. Basically the fields now render text (regardless of setting the data type to number). The gist of the notice ...
16
votes
2answers
90k 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@...
11
votes
5answers
32k 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)....
2
votes
1answer
973 views

Can anyone color code a column with an if statement?

Here is what I am trying to do. I have a SharePoint 2010 list that I want to color code dates based on that it expires 364 days after the date entered. Example: If the date is older then 334 ...
12
votes
5answers
67k 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 ...
4
votes
1answer
4k views

Calculated column with ID removes ID number on update

I'm using SharePoint 2013 and I have a calculated column in a custom list, called "Project ID" that has the following calculation: ="DS-000"&ID The first item in the list has the "Project ID": ...
1
vote
3answers
2k views

Count not updating in calculated column

I am getting number of days between Created & Now in SharePoint 2010 list using calculated column by below mentioned formula. =IF(ISERROR(DATEDIF(NOW(),Created,"d")),DATEDIF(Created,NOW(),"d")) ...
10
votes
5answers
71k 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?
4
votes
2answers
11k 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
7k 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
2answers
3k views

Sharepoint KPI using calculated columns

I am trying to create a KPI for the tasks schedule we have on a calendar. The formula I have is: "<div><img src='/_layouts/images/KPIDefault-"&IF(Completed="Yes",0,2)&".gif'/><...
2
votes
2answers
17k views

Use a LookUp field in a Calculated Column

I have a SP2013 Server but have not been authorized to install SPD2013. I have a list [Web Team Tasker] which has a lookup column containing data from a list on a parent site as well as some ...
2
votes
2answers
5k views

Using SharePoint version number in calculated column?

I'm trying to get the value of column "Version" in a calculated column. I'm not sure if it's a bug with SharePoint 2007 but every time a document is updated, the version number shown in the calculated ...
2
votes
4answers
13k views

Calculated Column to change text color in a custom list

I have created a custom list and with a column called Account, the column is a dropdown list and with following options Hourly Silver Gold I would like the options above to be bold and be certain ...
0
votes
3answers
685 views

Issue with calculated column

I have a calculated column CRM ID with simple formula =CONCATENATE("CRM")&ID. The formula concatenates ID field with string CRM. The issue is: Sometimes when user update list records, the CRM ID ...
9
votes
8answers
68k 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 ...
8
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 ...
8
votes
3answers
7k 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 OK ...
5
votes
1answer
28k 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"...
5
votes
5answers
29k views

URL in calculated column

Is it possible to have "clickable" URL address in (SP 2010) calculated column without any JavaScript code or Designer? I found the way how to easily make "universal" Document ID links visible for ...
2
votes
2answers
26k views

Calculated column- evaluating multiple conditions

Banged my head against this one for too long so thought I'd give in and post it. I'm trying to create a calculated column that evaluates two different conditions, as in: IF [Boolean A] = No OR [...
5
votes
1answer
15k 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 ...
2
votes
2answers
8k views

How to match current user to a username column?

I have a list view that should only show when an admin adds the person's username to an "allowed viewers" username column. So, I am thinking to create a calculated column that compares the current-...
2
votes
2answers
2k views

Display Sum of a calculated column

I have one calculated column and I want to display the sum of all its values but I know that's impossible in a OOTB way. How can I copy all the values to a new column (type number)? Do I need to use ...
0
votes
1answer
8k views

Calculate number of working days between two dates

In SP2013 calendar list I have calculated column that calculates number of working days between two dates. It gives accurate number of working days until user puts weekend day as End date, than it ...
13
votes
1answer
774 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 ...
7
votes
5answers
29k 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 ...
3
votes
2answers
6k views

Hide Calculated Column in SharePoint 2013

I am trying to hide a simple calculated column from forms in a custom list. However, unlike WSS 3.0, I am not able to achieve this simple task. Any way I take to hide it, the option is disabled. ...
1
vote
1answer
9k views

How can I use data from Managed Metadata Columns in Workflows and Calculated Fields?

It seems that sharepoint does not allow MM columns to be used in calculated fields, and I'm not sure how to use them in a workflow either. Can someone please inform me as to how I can use terms in the ...
10
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 ...
6
votes
4answers
7k 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
4answers
7k views

How to apply CAML query on the Calculated Column of the list?

I have a calculated column that stores the calculated date value. When I apply the CAML query, it does not return any row even if the record exists.
5
votes
3answers
19k views

Online - Hyperlink in calculated column

I'm using SharePoint online and I've followed this solution URL in calculated column to create an hyperlink in a calculated column. This is the formula I'm using: ="<a target='_blank' href='https:...
4
votes
4answers
9k 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 ...
4
votes
2answers
2k 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 ...
2
votes
3answers
4k views

Make dynamic calculated column Formula URL open as modal

Created a dynamic URL within a calculated column ="a href='/.../Test Doc Library/"&Title&"'>LINK</a> which works fine, but I need it to open as a modal and cannot get it to work. I'...
1
vote
1answer
10k views

replace text with formula in calculate

How can I replace ";" in a text like this with " AND " ? example text: "abc;cde;efg;ijk" the result should be like this: "abc AND cde AND efg AND ijk"
1
vote
2answers
3k views

What is the formula for calculated field to calculate the milliseconds of the field “Created” in SharePoint 2007

In SharePoint 2007 List, there is a field (or column) called "Created". It contain the date in which a item is created. I want to create any field, a calculated field, to help me calculate the ...
0
votes
1answer
545 views

Rendering calculated columns correctly

I'm tearing my hair out trying to get a % progress bar based on a calculated column in a list to work. I had it working for 24hrs, I tried to guild the lily and it's not worked since. We are on ...
0
votes
2answers
42 views

Capture Content Type selected at creation

I am trying to create a column that capture which content type a user selects to create an item in a list at the time of creation. I would also like to prevent that new column from changing regardless ...
4
votes
1answer
596 views

Is Possible to make filter on calculated column??

I have Date column in list on based it i have create two calculated column month and year. Now i am trying to get data for particular month and year so I have give filter in REST query but it give ...
4
votes
2answers
5k 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
1answer
4k views

Sum Calculated Column (SP 2013)

There is a very nice solution below from ViewMaster (http://www.viewmaster365.com/#/Create/Sum) that involves some scripting within the formula of a calculated column itself. However, the Sum will be ...
2
votes
1answer
2k views

Calculated field to show the greater of two calculated values

I have some documents that need to be deleted based on the following criteria: one year after termination, or three years after hire date, whichever is greater. I have created two calculated columns ...
2
votes
4answers
4k views

How to update calculated column on a daily basis without admin rights?

I am running SharePoint 2013 and have a column that is supposed to determine whether or not each phase of a project is late. I am using the NOW() fucntion, which, of course, does not update ...
2
votes
1answer
615 views

JavaScript Code in the SharePoint 2013 calculated column

I added the code below to a SharePoint calculated column in order to get today's date, and it works well, however, I will need to add another calculated column to get the date difference between "...
2
votes
1answer
2k views

Unique Validation on Calculated Column

I have 2 columns (first name and last name) in a SharePoint custom list. I have a calculated column called "full name" just concatenates the first and last name columns. Due to requirements, I need ...
2
votes
1answer
2k views

Why does my calculated column not pick up the correct ID

I'm trying to create a calculated column that uses the list item's ID field as part of the calculated string value. When I create a new list item, the calculated field seems to always use 0 as the ID ...
2
votes
4answers
4k views

Calculating a fiscal quarter from a date

I'm using the following formula to find the fiscal quarter (where the year starts in April) from a date in a list: ="Q"&INT((MONTH([Date of inspection])-1)/3) HOWEVER -instead of displaying Q4 ...