Questions tagged [calculated-column]

Calculated columns are the SharePoint equivalent of Excel formulas. They allow you to 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.

Filter by
Sorted by
Tagged with
43 votes
17 answers
33k 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 ...
user avatar
  • 431
42 votes
7 answers
122k 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 ...
user avatar
  • 4,466
18 votes
2 answers
108k 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@...
user avatar
  • 1,053
13 votes
3 answers
4k views

Sharepoint calculated column shows year with comma

I am new to SharePoint and I am trying to display year with the help of calculated column. However, the resulting year is displaying with comma i.e. 2,016 instead of 2016. Is there any easy way of ...
user avatar
  • 131
13 votes
1 answer
1k 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 ...
user avatar
11 votes
5 answers
88k 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 ...
user avatar
10 votes
9 answers
91k 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 ...
user avatar
  • 223
10 votes
5 answers
35k 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)....
user avatar
10 votes
2 answers
46k 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 ...
user avatar
  • 733
10 votes
5 answers
89k 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?
user avatar
  • 2,864
10 votes
2 answers
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 ...
user avatar
  • 121
9 votes
1 answer
17k 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 ...
user avatar
  • 383
9 votes
6 answers
47k 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 ...
user avatar
8 votes
3 answers
26k 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 ...
user avatar
  • 207
8 votes
4 answers
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 ...
user avatar
  • 683
8 votes
5 answers
44k 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 ...
user avatar
8 votes
6 answers
19k 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 ...
user avatar
  • 161
8 votes
5 answers
33k 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 ...
user avatar
  • 6,725
7 votes
3 answers
42k 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:...
user avatar
  • 1,053
7 votes
3 answers
9k 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 ...
user avatar
  • 251
6 votes
4 answers
8k 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 ...
user avatar
  • 1,021
6 votes
3 answers
6k views

Difference between two dates in hours

In SP list there are two date/time columns, column A and B. I want to calculate difference in hours between those two dates, but only on working hours. My working hours are between 8AM (08:00) and 4PM ...
user avatar
  • 1,749
6 votes
2 answers
1k views

Calculated Column Based on Version Not Updating

I'm using a calculated column [DocumentStatus] and versioning to try and do some colour-formatting based on Approval Status. I have Major & Minor versioning turned on. Since I cannot use Approval ...
user avatar
  • 163
6 votes
1 answer
447 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. ...
user avatar
5 votes
1 answer
9k views

Time Difference Formula in Calculated Column

How Can I achieve displaying time difference between two type "date and time " columns? I want to put the time difference in another column type "based upon calculation" of those two. I want to ...
user avatar
  • 135
5 votes
2 answers
8k 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. ...
user avatar
  • 1,480
5 votes
2 answers
25k views

Calculated column issue with if statement and yes/no field

I have seen other similar questions about calculated fields/columns and Yes/No fields, but only regarding syntax. I don't have an issue with the syntax (I think) but I can't get an IF statement to ...
user avatar
  • 183
5 votes
3 answers
31k 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] =...
user avatar
  • 936
5 votes
4 answers
5k views

What is the workaround for limit in nested if statements in sharepoint calculated column?

I have to create a list wherein when user inputs a data to a column the corresponding calculated column should take the data and analyze the condition and then return the value, but my SharePoint ...
user avatar
  • 71
5 votes
2 answers
254 views

=TEXT([Date Column],"MMMM") issue

I'm using the calculated column formula below: =TEXT([Date Column],"MMMM") This translates my date in the date column (ie. 01/01/2017) into "January" in this calculated column. However, if I leave ...
user avatar
5 votes
3 answers
792 views

Sum of two Time columns

There are two calculated columns that display times of two date columns. I want to sum those two "time" columns. Any suggestion is greatly appreciated. Example: column 1: 16:35 column 2: 14:00 So, ...
user avatar
  • 1,749
5 votes
1 answer
16k 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 ...
user avatar
  • 1,021
5 votes
2 answers
9k 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 ...
user avatar
  • 429
5 votes
1 answer
2k views

Easy way to write a correct formula!

Is there any easy way to write and validate a SharePoint calculated field formula? I am trying to find a formula builder like CAML builder!
user avatar
5 votes
1 answer
35k 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"...
user avatar
  • 53
5 votes
1 answer
3k 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 ...
user avatar
  • 693
5 votes
1 answer
63 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 of ...
user avatar
5 votes
1 answer
5k 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": ...
user avatar
  • 167
5 votes
4 answers
13k 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 ...
user avatar
  • 1,472
5 votes
3 answers
10k 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.
user avatar
4 votes
5 answers
34k 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 ...
user avatar
  • 1,614
4 votes
4 answers
11k 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 ...
user avatar
  • 3,165
4 votes
2 answers
368 views

Calculated column to show status if another has a value

I have a SharePoint list with a completed date column. I wish to have a calculated column called status so when the completed date column has a date in it, it shows "Closed" and when it is blank it ...
user avatar
  • 57
4 votes
2 answers
14k 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 ...
user avatar
4 votes
4 answers
14k views

How do I show the count of certain types of SharePoint List columns?

I would like to know how can I can sum the values of a specific column across multiple rows in a list. For example: I have 3 rows of camera entries and 2 rows of keyboard entries. I would like create ...
user avatar
  • 73
4 votes
2 answers
6k 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 &...
user avatar
4 votes
3 answers
2k 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 ...
user avatar
  • 447
4 votes
3 answers
34k views

How to convert date field in single line of text using Calculated columns

I have created a calculated field for a content type in my Sharepoint 2010 and I want to return a date in the TEXT format because I am returning some text with it. The condition I have put in the ...
user avatar
  • 143
4 votes
2 answers
871 views

Traffic lights on a SharePoint 2010 list

I'm trying to set up a Traffic List priority list on my SharePoint 2010 list. I'm following this Blog: https://blog.splibrarian.com/2012/06/06/using-calculated-columns-to-add-color-coding-to-your-...
user avatar
  • 767
4 votes
1 answer
853 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 ...
user avatar
  • 489

1
2 3 4 5
24