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-formula]

Question related to the Calculated Column

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 ...
13
votes
1answer
775 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 ...
11
votes
3answers
2k 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 ...
9
votes
1answer
8k 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 ...
8
votes
1answer
6k views

Filter “Modified” by previous week

Want to filter list items that were modified on last week Think pseudocode for this will be Modified >= ( [Today]-WEEKDAY-7 ) && Modified <= ( [Today]-WEEKDAY ) No matter what day of ...
6
votes
1answer
342 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 ...
6
votes
0answers
95 views

Formula in spreadsheet being converted to a custom list

I am currently trying to take an existing spreadsheet and replicate this as a SharePoint list. The spreadsheet contains the following columns: Received - This will be a number column with 0 decimal ...
5
votes
1answer
55 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!
4
votes
3answers
31k views

Formula to show calculated field as blank when a certain result is not desired

I have a very simple formula to calculate the number of days between two dates in a Sharepoint list =[Action Closed]-[Action Assigned]. It returns the desired result when both columns are populated, ...
4
votes
4answers
1k 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 ...
4
votes
4answers
554 views

How do I create a calculated conditional column using today's date, and automatically update each field daily?

I have created the following list (Timeframe) in SharePoint; Timeframe Start Date End Date Another column, [Is Current Timeframe], needs to be created that specifying whether or not the Timeframe ...
4
votes
3answers
1k views

Calculated column - calculate percentage done based on status

I'm trying to create a calculated column based on a status column. If the status is "planned", I'd like it to return 5%, if it is "writing", it should be 30%, if "technical review" it should be 50%, ...
4
votes
3answers
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 ...
4
votes
2answers
17k views

concatenate two List calculated columns into one

I am new in SharePoint so please forgive me if I am not using the right terminology. I have a list with several columns (some are calculated columns) and I was doing fine until I hit my column ...
4
votes
2answers
105 views

SharePoint Calculated Column

Im no expert but I seem to be struggling with this. I have a calculated column field called [Review Days] for which I hope to enter an amount of days that a document will need reviewing. 1 year will ...
4
votes
1answer
863 views

Add a line break in calculated field with formatted date?

I am trying to create a line break within a calculated column that displays the Date in DD MM YY format. The purpose is to utilize the field in a script which is altering the list view. My ...
4
votes
3answers
8k views

Calculated Column Formula how to replace Substitute

I need to grab everything after the last "-" (strings can have up to four "-") and if there is a space after the last "-" to not take the space. Examples: TEXT -(THIS VALUE) TEXT - TEXT -(SPACE)(...
4
votes
1answer
3k views

How to filter tasks for previous calendar month

How to setup view, that would show all "Tasks" for previous month ? I can find first and last day of previous month. End of previous month: End_of_previous =DATE(YEAR([Activity Date]),MONTH([...
3
votes
6answers
24k views

Days since list item was created

I have a list. When an item is created it is automatically assigned the created date. I would like to add a “days open” column. "Days open" being the number of days from the created date to today’s ...
3
votes
1answer
720 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
280 views

Can someone spot an calculated column error Please?

I am trying to create a calculated column where 'Today Date' is another date column. The first two block are working fine till the [Enter time]=2 condition when I add other two it says syntax error. ...
3
votes
2answers
88 views

How can I validate number column

I have number column in my new form. If user write here number, I need to validate if it is multiple of value 0,5. For example 1 or 2,5. I've tried formula for column validation: = ?*0,5 but it ...
3
votes
1answer
599 views

Correctly url path?

I upload photos into my sharepoint online into assets folder, when I go to that folder navigator bar shows: https://mybusiness.sharepoint.com/siteassets/Forms/AllItems.aspx?id=%2FSiteAssets%myFolder%...
3
votes
1answer
11k views

Count number of rows in another list

I have two lists, A and B. I wish to find out the number of rows in list B that have a certain value in list A (through a lookup column) and display this in list A. Is this possible at all using a ...
3
votes
1answer
1k views

SharePoint 2013 - Color Code Text in a Column

How would I color code text in a column when an overdue date threshold is reached? I created a calculated column (# days overdue) that shows total number of days the item has been open. When the # ...
3
votes
1answer
285 views

Follow up based on weekdays

I have a list with a Date Column and a Followup column. If the weekday of the date column is Thursday, the follow up should be on Monday next week. If the date column is Friday, follow up on Tuesday, ...
3
votes
1answer
637 views

Calculated Column checking against a multi-selection lookup

FieldA is a lookup (to another SP list) with the option to select more than one choice from 9 options: let's call them 1 thru 9. FieldB needs to be a calculated field that evaluates FieldA. Certain ...
3
votes
1answer
272 views

Need help with calculating a date that only falls on a weekday

I have a column named "Date received" and I need to calculate a "Date Due". The due date is 3 calendar days after received however if that falls on a weekend it needs to be the next business day. I'...
3
votes
1answer
72 views

how to get minimum dates in a sharepoint list

How can i get a minimum date of some columns with dates field.I know i can use the min function(formula) with numbers but not with dates. is this possible using calculated columns if not would i be ...
3
votes
1answer
181 views

Calculate Hours Worked Per Week

I'm currently using two lists, one stores tasks [Task List], the other stores the hours worked for completed tasks [Time Sheet List] When a user marks a task as complete, it creates a corresponding ...
3
votes
3answers
1k views

Concatenate two dates to one column

I am pretty basic user of SharePoint 2013 and I have this problem: I have to create a calculated column with two dates in them to look like this: From 21/2/2016 to 5/3/2016 but whatever I try, the ...
3
votes
2answers
9k views

if and else if in calculated field

I have a column in SP called Pursuit status that contains various states a proposal can be in. For a diffrent project I need to lump them into 3 buckets (win - loss - pending) The fields below are: ...
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 ...
3
votes
3answers
296 views

Is there a way to calculate the number days between two list items in a list?

I have an InfoPath library/list in SPO. I need to calculate the number of days between two different list items. It's to show the number of days since the last work event. Is there an easy way to ...
3
votes
1answer
65 views

Yes No calculated field for closed/created in last 7 days and items open or in-progress

I would like to create a calculated field that outputs "Yes" or "No" if an item in my SharePoint list has been created in the last 7 days, closed in the last 7 days (field in status column), or 3) ...
3
votes
0answers
102 views

SharePoint Conditional Validation / Formula

In SharePoint Online can i perform the following? I have a column to calculate the date difference between Last Visit Date and Next Visit Date. Last Visit Date is already provided, and users will ...
3
votes
0answers
6k views

Calculated column formula for not equal

Hi can I use the <> operator in a SharePoint Calculated column? The following formula is accepted but I'm not sure it is supported. My requirement is: If any values equal High AND are not ...
3
votes
0answers
611 views

Sharepoint 2010 Nested If Limit

In SharePoint 2007 the limit for the number of nested IF statements was 7. On the 8th SharePoint would return and error. On my 2010 site I made an error and added 8 nested IF statements and there was ...
2
votes
4answers
16k views

Calculated column for differences between two dates with blank check

I am trying to create a calculated column which shows the number of days' difference between two columns which have dates in them. I understand that I can get this with the following formula: =...
2
votes
1answer
429 views

Show only number before decimal in calculated column without rounding the number

Application : SharePoint 2013 I need to show only part of number before decimal nn calculated field that is looking on number column. And Calculated column results should be shown without rounding ...
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
5answers
4k views

IF statements in Calculated List Column always returns “The formula contains a syntax error or is not supported”

No matter how simple I make my formula I always get an error when I try to save a new calculated column with an IF statement in it. For example: =IF("Foobar"="Foobar", "Foo", "Bar") On save I get "...
2
votes
2answers
130 views

Calculated columns formula today not update (SP 2010)

Created a field with the formula Today + 2 days, checked the next day, the date is old, i.e. in which this column was created, I need the date to be updated every day, how to implement it?
2
votes
2answers
730 views

Can't get calculated column syntax right

This "ought" to be easy and yet as with so many things SharePoint, it isn't. I have a calculated column where I keep getting a syntax error. If the user sets a dropdown column value to x, then y ...
2
votes
1answer
123 views

Calculated Column IF/AND question

What did I do wrong? I need a calculated column to return the following: If [Status] equals "Pre-OPEN" then return [Date Opened]+3 If [Status] equals "OPEN" then return [Date Opened]+30 If [Status] ...
2
votes
3answers
469 views

trouble with calculated field

I am experiencing a couple of problems with calculated field in document library. Problem one: Calculated field gives me this error: The formula refers to a column that does not exist. Check the ...
2
votes
2answers
2k views

How do I add hours to Now() after converting it to a text value?

I have a calculated field that shows the text value of the current hour, so I'm using: =TEXT(Now(), "hh") Unfortunately, it's showing this value in Pacific time, so I'd like to add 3 hours to ...
2
votes
2answers
3k views

Dynamic Hyperlink via Calculated Columns Truncating at Space

I'm trying to generate a hyperlink to an item page using a calculated column. I'm 99% of the way there, but I run into issues when the title has spaces. My calculated column has the following ...
2
votes
3answers
3k views

Javascript Functions in Calculated column

I am trying to use javascript functions within an calculated column which is working pretty fine e.g. if I use the following formula: ="<img src='/_layouts/images/blank.gif' onload=""{" &" ...
2
votes
1answer
28 views

Replace comma with an underscore calculated field SharePoint Online

I am trying to replace a comma from a single line of text field with an underscore(this I managed to do), but I want to replace it if a certain criteria is met - if the value in the field contains a ...