Questions tagged [calculated-column-formula]

Question related to the Calculated Column

Filter by
Sorted by
Tagged with
2
votes
2answers
68 views

Show image based on calculated column value

Why does SharePoint not understand to change the image when I use If statements? Specifically when I use it on a calculated culumn. Building a status (project/risk) page and I use Probabiliy and ...
2
votes
2answers
118 views

Replace second “?” with Calculated Column

The string below contains two "?" what calculated column formula should I use to replace the second "?" with "&" but not remove the "?" from other strings that have only one "?" Could anyone ...
1
vote
1answer
62 views

How to correct below calculated date field's formula for SharePoint list calculated field?

I have a date field called Holiday Date which is populated by user. Based on this date field, I have another calculated date field as Holiday Accrual Date. Now let's say if the populated date in ...
1
vote
1answer
39 views

How can I add a column in a list where I can choose several items in the list then calculate the difference between them? [closed]

as an example: I want to choose the percentage of how complete is the task ( in percentage) of an employee who works in 3 projects then calculate the difference between them. (100% - (the total of the ...
-1
votes
1answer
128 views

How to indicate a blank space in Column Validation?

I have a column called Machine #. There is a space between Machine and the # symbol. I think the space might be causing an issue - Sharepoint does not seem to like having a space in the Column ...
6
votes
1answer
369 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 ...
0
votes
1answer
23 views

How do I create an formula in SharePoint Online?

hope you're well; I would like to know how to create a formula for weekly in SharePoint online. For example Tax year is April 2018 but I would like weekly number to be from Tax year.
1
vote
1answer
94 views

Calculated Column Syntax nested DATEIF and ISERROR

This is for SharePoint Online I have a calculated column that takes info from 2 date columns and calculates the number of days. Some times one of these columns wont contain data. This will then ...
0
votes
1answer
239 views

DATEDIF function to also work with negative numbers?

I am trying to write a very simple equation as Calculated Column on SharePoint List. This equation works easily in Excel, but I'm having trouble with it on SharePoint List. I am trying to find the ...
1
vote
1answer
64 views

SharePoint Online- Column Calculations Getting Total Items Missing

This is for SharePoint Online Having trouble getting column item count of empty entries. Originally I was attempting to find the total number of items missing in a column. example: Column A has ...
1
vote
1answer
23 views

How to create a calculated column formula for CPE year

I've been trying to figure out how to create a formula that will show what CPE year a user's training is in. Similar to fiscal year, which I did try FY formulas that are out there to no avail, a CPE ...
0
votes
1answer
262 views

Yes/No formula for Calculated Column Type

=IF(Fueler,"=Date(YEAR([Fueler Date])+2,MONTH([Fueler Date]),DAY([Fueler Date]))"," ") The type is yes or no. I would like to know if there is a way that if no is chosen, it would leave the ...
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 ...
2
votes
1answer
65 views

How to design calculated column formula for multiplication based on nested IFs?

One of the SharePoint forms I am working with, i have two columns: !. Choice Column with choices: 2.5 ,3,4 and 6. The second column is called Quantity of type number where user enters a value in ...
1
vote
1answer
583 views

SP2013 - Formatted number column

I have a list where a text column is created for people to enter ID numbers. These ID numbers are six digits long, and numbers-only (no letters, no symbols, etc). I noticed that people were entering ...
1
vote
1answer
2k views

BRAG status for task list status - Words not Icons

Hi I'd like to ask a question as I've been struggling with this for some time now. The scenario is a I have a task list. and I want to add a simple BRAG status, I’m not looking for icons at this ...
1
vote
2answers
67 views

Difference Between 2 Dates (number of hours beyond 24 hours)

Trying to get some working syntax for calculating the difference in hours between 2 dates, Start Date [9am] and Due Date [5pm] for example. =TEXT([Due Date]-[Start Date],"h") This yields the correct ...
0
votes
1answer
32 views

If condition in calculated column

I have a small problem to solve and I hope you guys can solve it easily. So currently I have a calculated column which calculates the value but only when the status is available, so it looks like this ...
1
vote
2answers
457 views

SharePoint Calculated Field (DATEIF)

I'm using SharePoint 2010. Right now I have a calculated column which displays in days the difference between ColumnDate1 and ColumnDate2 as follows: =DATEDIF([ColumnDate1],[ColumnDate2],"d") But ...
0
votes
1answer
42 views

Column requires refresh upon saving

Good morning, I have a column with the following calculation: ="PX-"&CONCATENATE(REPT("0",3-LEN(ID-6)),ID-6) I had to use 6 to offset the ID number to provide a PX number. The problem is that ...
0
votes
2answers
408 views

Format a single line of text using JSON

I have a single line of text column in a sharepoint online list that is being populated with a dynamic value added by a workflow (the value is a url + the current created item ID added to it). And as ...
0
votes
1answer
23 views

Help with SharePoint calculated columns

I am trying to get a calculated column to work, I have the first part working fine =IF([Week Day]="Monday",[Week Ending]-6) So if weekday is Monday then take 6 from week ending and that is todays date....
0
votes
1answer
26 views

To many Parens on calculated field?

Am I overdoing it with parens? =IF((app_pr_apr01_status=""),"",IF((app_pr_apr01_status="approved"),"approved","pending")) Basically if app_pr_apr01_status = "" then field = "" if ...
0
votes
1answer
46 views

SP List dropping date format

I have a sharepoint list that has a timestamp in one of the columns. I'm adding the timestap via MS Flow. I need the format to be yyyy-MM-ddThh:mm:ssZ (so i can compare later in a REST call) but ...
1
vote
1answer
31 views

Calculated Column Generating before Other Columns?

I am trying to create a unique identifier using a combination of a few letters and numbers, for example: abc1000, abc1001, abc1002. The text will always be the same and the number is based upon the ...
2
votes
1answer
3k views

Add month in calculated date column

I'm using SharePoint 2007. I've a column with date in format DD/MM/YYYY I've also another column where I want to add some months to the previous date and obviously I've a column with the number of ...
0
votes
1answer
42 views

Calculated Formula how to make it more succinct

I want to do the following: If Risk Level = 0 "Accepted", if Risk Level = 2, 3 or 4 "Watch", if Risk Level = 5, 6, 7, 8, 9, 10 or 11 "Plan", if Risk Level >= 12 "Treat" I have got this so far: =IF(...
0
votes
1answer
34 views

My If/Then formula is not working

Here is the statement I used: =IF([State]=OH, "[PRAC Comm]*0.05", "0"). What I am trying to do is calculate an amount based on the state. If the state is OH, then I need to multiply the amount in ...
0
votes
1answer
25 views

Render one or another column only if data is present? [duplicate]

I'm a relative newbie working on SP2013 w/o SPD. I have a lookup column for NAMEPD and a text column for NAME NOT FOUND on the same list. NAMEPD is a simple dropdown choice, NAME NOT FOUND is there ...
1
vote
1answer
71 views

How to resolve rounding error in calculated column involving Date and Time?

I have a custom list in SharePoint with two Date and Time columns, namely StartTime and EndTime. Now I want to calculate the difference between them, so I created a new calculated column Length and ...
1
vote
1answer
40 views

schedule tasks count in project server

I want to write custom field formula in project server to get the tasks schedule count for each project and show it in project summary view in the project center Please, Help me
0
votes
1answer
29 views

Calculated column for sum of last three values

I need to create a calculated column that returns the average of the last three values in a column. much like the offset function in excel
2
votes
4answers
17k 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: =...
1
vote
2answers
190 views

Calculated Column Formula Syntax Error

I am trying to setup a list that I can use to calculate deadlines for an approval process. I was asked to set the deadline to 5 working days, and exclude the 2 weekend days. I found a really helpful ...
0
votes
1answer
420 views

Sharepoint 2010 Calculated Column: How to get value before item gets modified?

I have a list that has a Status-field. If the status changes, a modified date in another column should indicate, when the status has been changed. My idea was to create a duplicate of this Status-...
1
vote
2answers
58 views

Calculated Date Issue

I created a calculated column and enter this formula: =DATEDIF([Column 1],[Column 2],"d") I picked the columns from the list to the right of the formula block. When I entered this, it gave an error ...
0
votes
1answer
22 views

SharePoint Online, “Dec 31 [this Year]” calculated field - how?

Trying to create calculated field for SPD workflow. There should be yearly reminder (I wanted to compare "Date workflow started" with this field..). Tried to create Date field/Calculated field with ...
1
vote
2answers
480 views

How to Conditionally Require Data in more than one SharePoint Column

I am attempting to calculate the outcome a status field based on the outcome of other columns. They are all Test Fields. For Example: "Overall Approval Status" column is to be based on Accepted/...
3
votes
1answer
302 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, ...
1
vote
1answer
49 views

Sharepoint List Calculated field - formula help!

I have this formula working fine, but when field VD (which is a date field) is empty, it adds a funny date to it. How can I get it to us the VD field when populated, but if its empty just display TBC ...
1
vote
1answer
3k views

How to create a calculated field and use it in a list definition?

Here is my code: <?xml version="1.0" encoding="utf-8"?> <Elements xmlns="http://schemas.microsoft.com/sharepoint/"> <Field ID="{A57D77CA-9D80-458A-AB81-884C4F5A946D}" Name="...
1
vote
2answers
64 views

How do i get a choice column to change from YES to NO depending on another column using calculated columns

I'm trying to uses nested ifs to change what a choice column displays but I'm not sure if this can be done or if I'm doing it properly. What I'm trying to have accrue is once the next review date ...
1
vote
1answer
150 views

Perform a calculation based on other criteria

sorry in advance supernoob here failing hard and need help. I'm trying to create a timesheet in sharepoint that will eventually be a powerapp then go into PowerBI and I'm struggling bad with the ...
1
vote
2answers
385 views

Extract data from meta data field and copy to new field

I have a managed term set which provides date meta data for a column used by accounts. They tag a document depending on the period (month and year) it relates to. What I wish to achieve is to have 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 # ...
0
votes
1answer
39 views

Attempting to get one out of four possible status based on due date

I have a SharePoint list and the three columns I am working with are Status (shows either complete or not complete), Due Date (shows when an item is due) and a new column (this is the calculated ...
1
vote
1answer
29 views

Getting Sharepoint to calculate columns and render them as a | delimited

This a great online community! I am trying to get columns to calculate and then display the calculated value in delimited format. Example is [COLUMN 1]+[COLUMN 2]+[COLUMN 3] The calculated result I ...
0
votes
1answer
98 views

Calculated field on List View

I've set up a Calculated field up on a view with the following formula: =ID+800 At first it's fine. It shows incremental numbers (1001, 1002 etc) but after around two items are added or the view is ...
1
vote
1answer
1k views

Calculating Week Number in calculated field

I'm trying to calculate the week number based on a fiscal year starting on November 1st with moderate success. Current formula: =INT(([Project Start Date]-DATE(YEAR([Project Start Date]-WEEKDAY([...
1
vote
1answer
800 views

Creating KPI Status Indicators with Calculated Column

I am trying to use calculated columns to have a possibility of 3 status indicators come up based on another column that is called projectstartday which shows how many days are left until a project ...