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.

Filter by
Sorted by
Tagged with
0
votes
1answer
6 views

adding years to existing date to produce expiry date ( SP list)

I have a list of training course that all have different levels of validy. So course A lasts for 3 years Course B lasts for 2 years Course C lasts for 1 year At the moment I have a column with ...
3
votes
2answers
120 views

If exceed 24 hours show “23:59:59”

So I got this calculated column called "Due time" with this format in it: =IF([Hours]<24&&[Minutes]<60;CONCATENATE([Hours]&":";[Minutes]);"23:59:59") I only get syntax error with ...
0
votes
1answer
14 views

Averages of Column values placed into either its own column, or workflow variable

I've researched this one - and while I have found many helpful articles CLOSE to it, nothing hits exactly what I'm needing. I'm trying to populate either a variable in a workflow OR a column with the ...
0
votes
1answer
18 views

COUNT of items in list, with more requirements

Here's my current predicament: I'm trying to assign a COUNT value in one list to a column in another list (on the same site, of course). But, here's the twist - I want a COUNT of columns of a certain ...
0
votes
1answer
17 views

Calculation Overload from Excel to Sharepoint

How can I convert this Excel Formula to SharePoint? =IF(AND(F6="REG",G6>=4,G6<8),0.5,IF(AND(F6="REG",$G6>=8,G6<=16),1,IF(AND(F6="HDY",G6>=4,G6<8),1,IF(AND(F6="HDY",G6>=8,G6<16)...
1
vote
2answers
21 views

View only latest added items if item already exists (same name)

Need some help figuring out how to go about this! I have a list, people will add items to it, sometimes with the same title (which is correct). We then group these items by the column 'title' on the ...
0
votes
1answer
16 views

Compare times in calculated column

I want to compare the time from a column called "Date and time" (datetime format) with a fixed time value. I have created a calculated column for this. =IF(AND(TIME([Date and time])>TIMEVALUE("06:...
0
votes
2answers
44 views

Have a column pull criteria from two columns and display specific value

My goal is to have a calculated/or lookup column to display the company number if two columns contain certain information. I do not have access to PowerApps, Microsoft Forms, SharePoint Designer, ...
0
votes
1answer
10 views

Calculated column, one column is what percent of another column?

I'm having a bit of trouble figuring out the calculation in a calculated column. Here's what I've got - I have two NUMBER columns (column A and Column B). What I want is to calculate "Column A is ?? ...
0
votes
1answer
56 views

Need help to extend value to seconds

Got a calculated column with this formula: =IF(INT(Minuter/60)<10;"0"&INT(Minuter/60);INT(Minuter/60))&":"&IF(ROUND((((Minuter/60)-INT(Minuter/60))*60);0)<10;"0"&ROUND((((...
0
votes
0answers
16 views

Calculated Column in SharePoint 2007 list stopped working

I have a calculated column called 'Month' which adds in the month and year when each item was last modified. This has suddenly stopped working across all SharePoint 2007 lists in our environment and ...
0
votes
1answer
27 views

how to Insert multiple items into calculated column from single line of text

My single line text value is Colombia|d12fd403-83ac-4982-bc2c-3efdeb86e1f1; EP|1ac55076-7d65-4846-af18-67658fdf276a;Bronswerk|a5e873d7-61b6-454c-98cd-8c30ec17604f. I want to get the output in ...
1
vote
1answer
53 views

If calculated value column exceeds 24 hours

I got this calculated column that shows values of "hh:mm:ss". But i've noticed that if its exceeds 24:00:00 it will restarts to ex. 01:00:00.. Is there any way to make a validation or format the ...
0
votes
1answer
49 views

How to make a calculated column blank if other columns doesnt have any values?

This is my format in the calculated formula: =TEXT([Column2]-[Column1];"hh:mm:ss") I have value "2018-12-13 10:10:23" in Column1 but when Column2 is blank the calculated column shows "10:10:23" ...
0
votes
1answer
26 views

Else If Calculated Column

Hi there I am just looking to see if this is possible ? I want to make a column such that if you choose a certain value from a choice column then it will give you another lot of values to choose from....
0
votes
1answer
55 views

Calculated Column - Sharepont

I used calculated column with "IF" & "AND" Functions, If the logic is true or fall certain comments will be appeared to users// Comments "Verification Success", "Verification Failed". When I try ...
0
votes
1answer
34 views

SharePoint Online List Calculated Field HTML Tags is not working

in SharePoint migration process we found a list having two calculcated fields. 1. Color: using if condition based on field values, field will get/Set the color. 2. Display: will display the Title ...
0
votes
0answers
58 views

I want a column to become either RED or GREEN depending on different choices AND timestamp

SO.. This is kinda advanced I think? I'm kinda bad at this and doesnt know where to start. I want the column "Inställelsetid" to become either RED or GREEN depending on different choices AND ...
0
votes
1answer
27 views

Help with using Caml in rest api calls - filtering doesn't work for me

I'm trying to filter a Posts list(Blog) by month and year. I created a calculated column and used the Published Date to extract month and year. But my below code doesn't filter correctly when i use ...
0
votes
3answers
31 views

Caluclated Column If statement with Dates

I need to create a calculated column in SharePoint with a text output but it includes a date+day equation. I am getting syntax error which I believe is due to the column wanting the output to be a ...
1
vote
1answer
20 views

Calculating Fiscal Month

I've seen a number of calculations for fiscal year and quarter, but am hoping someone can help me a calculated column for fiscal months. Our company's fiscal months run from the 29th through the 28th....
0
votes
2answers
57 views

Apply the friendly date format to a calculated column in a SharePoint list

I have a calculated column [Due date] with a simple formula, =[Event date]+13, and it's set to The data type returned from this formula is: Date and Time. However, it doesn't have the Display Format:...
0
votes
2answers
27 views

If and else if in sharepoint list calculated column giving syntax error

I have two date fields date1, date2 and stores in column name result . I need the difference of the two dates to be calculated and store no of days in the result. Problem here is with two cases: ...
0
votes
2answers
87 views

I can't find a solution to use the value of a lookup column in a list, can anyone help?

I am trying to create a kind of registration tool with a share point list (next step could be to create an app), but I am struggling to get the value of a lookup column. I read a lot of forums and ...
0
votes
1answer
26 views

Cannot seem to access Calculated column in Display Template via Managed Property mapping

Here is my situation: I have a test/development site on SharePoint Online/Office 365 called TestSite3, and within that site a sub-site called TestBlog3 (a Blog site for making Posts). I want to use ...
0
votes
2answers
39 views

Calculated Column Field Date

I need to calculate date based on other column I'm using =Date(Year([endDate]),1,1) in a calculated field which needs to return data as DateTime, but it is giving me error: The formula contains a ...
0
votes
3answers
31 views

Formula not working in Excel to move to SharePoint calculated column

I have two conditions that need to be met to return a date in another column Here is my formula =IF(ISNUMBER(SEARCH("Rejected",A2)) =IF(isblank,"","Today")) I can't get this to work, if i do them ...
1
vote
1answer
14 views

Calculated column with multiple options depending on start date of event compared to today

I'm attempting to create a column ("30, 60, 90, +") that reports if an entry is within 30, 60, or 90 days out. One field is "Start Date" and I want the field to report '30' if the start date is ...
0
votes
0answers
16 views

SharePoint Calculated field Failed to update value on all items on Large List

So this is the problem: I have a list with around 1 million records I added a Calculated Column, with a very simple formula: "0" + [textfield] and return it as number The request returned an ...
1
vote
2answers
30 views

=IF(DATE(YEAR([Compliance Date]),MONTH([Compliance Date])+11,DAY([Compliance Date])), “Due”, “Not Due”)

I am trying to create an If statement that looks at a date column in sharepoint and if its 11months after that date the column displays yes. any help on this as: =IF(DATE(YEAR([Compliance Date]),...
0
votes
1answer
27 views

Create a calculated column to be yes IF([due date]=[Today]-31, “yes”, no) or custom view

Im trying to set up a calculated column to look at another calculated date column and if its under a month (ie its due to be renewed) than say yes. if i cant do that can i create a custom view from a ...
0
votes
2answers
13 views

Can't get conditional syntax correct

Have [Tier] column with choice values of 0,1,2,3. If [Tier] = 0 calculated column should = 0 If [Tier] = 1 calculated column should = 4 If [Tier] = 2 calculated column should = 24 If [Tier] = 3 ...
0
votes
1answer
38 views

Update a column if Date in another column has been breached?

I am trying to update a column value IF the Date presented in another column has been breached. I will then need to format the row the has breached this date in Red. The columns are: Date ...
0
votes
1answer
34 views

Adding a calculated column to a Large List — how much time it will take?

I have this question because we have a Production deployment and we need to update some lists with about 1 million items stored on them The calculated field formula is very simple :take 1 field and ...
0
votes
1answer
30 views

Calculate max(num) +1 with calculated value? (Help please)

I want to do this: MANUEL 1 PEPE 1 MANUEL 2 LUIS 1 I want to get the number by a calculated value, but if the name is duplicated get the max(num)+1. Example: if (NEW.INPUT) exist find max(...
0
votes
0answers
28 views

Sharepoint 2013 Button in List View to Start an SP 2010 Workflow

I was wondering if someone could help me to get this feature in SharePoint list view. I want to get a button in Custom List View itself with a Calculated Column to start an 2010 SP Workflow. I ...
0
votes
1answer
47 views

Calculating “Task Aging” without weekends

I am calculating the number of days a task is aging. This works great: =DATEVALUE(TEXT(NOW(),"mm/dd/yyyy")) - DATEVALUE(TEXT([Created],"mm/dd/yyyy")) However, I need the counting to STOP when the ...
0
votes
2answers
26 views

String Calculation SharePoint 2010

I have the below string stored in a SharePoint Column: 11608,613,12,12643,12644,12656 I want to extract the value after the last comma. That is I need to extract only 12656 and use it for other ...
0
votes
1answer
22 views

Value of the Yes/No Column

What is the values of the Yes/No Column in SharePoint? 1, 0 Yes, No "Yes", "No" True, False Trying to use calculations: =IF([Col1]=Yes, 1, 0) So if it's true, put 1, else 2. But not matter what, ...
1
vote
3answers
29 views

SharePoint 2013 calculated column -compare dates

I thought this is easy but somehow I didn't get this right. I entered the following formula in the calculated column but the outcome is incorrect. For example, when [Target_Finish]=10/10/2019, the ...
0
votes
2answers
17 views

Calculated Column with IF ISBLANK, else if “ABC”, then return “XYX”

If [OH:Subsample ID] is blank, OR if it contains "ABC", then the calculated field should return result "XYZ". No idea how to put that into syntax. I can get two separate lines, but can't get them ...
0
votes
1answer
27 views

Calculated Column Not Displaying Anything

I have this really odd issue that works in 1 document library (first image) and doesn't for another (second image). I have 2 columns, column 1 called Published Date (Flow) that is of type string (...
0
votes
1answer
35 views

Calculated Date Column - Multiple IF and more

I've been trying to work this out for 2 days and I'm probably overcomplicating this but here's what I'd like to do. If the employee has been employed >= 2 years they only need to take certain classes ...
0
votes
1answer
24 views

How can I populate a Library or List column name with the name of a file that has been uploaded?

Using SharePoint Online, how can I populate a column with the name of a file that has been uploaded? Seems all the related Q&A I am finding have more complex twists, this I hope is straight ...
1
vote
1answer
25 views

Extract different text values from the title using calculated column

I am looking to find a way to extract text from the TITLE of an item when that text value could be different. Example: TITLE = N10 2019-2020 Kilo Project R Sec All Data Calculated column called ...
0
votes
1answer
37 views

Exclude weekends

I am working on a vacation calendar project. I have a custom lists with 2 columns, one being StartDate and the other NumberOfDaysTaken. I am using a simple SharePoint formula (=FromDate + [How many ...
0
votes
0answers
10 views

SharePoint 2013 Convert time pulled by JavaScript to different time zone

I have a text column in a list that receives the value of a JavaScript timer that's attached to a SharePoint survey that populates the column in this format: 2019-08-28T16:56:19.531Z Can I create an ...
0
votes
1answer
32 views

Calculation in Calculated Column varying based on null values?

Here's one I've never dealt with before: I have calculated columns (in a SharePoint 2013 survey) that calculate values based on particular survey questions. One of those values, however, is an "N/A" ...
0
votes
1answer
28 views

How to calculate the average of a field using a date range

I need to calculate the average of a number field called "Total" over a date range. I have a To and From date picker. I'm sure this can be done in Sharepoint, but I have no idea of the logic to use. ...
1
vote
1answer
24 views

Calculated Column Depending on Different Row Value

In our department, we can make offers with different packages. These offers are sometimes only one device and sometimes set. If the proposal (project number) package was submitted in a way, it should ...