Questions tagged [calculated-column-formula]

Question related to the Calculated Column

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

Showings days past due and days until due in same field

=IF(ISERROR(DATEDIF(NOW(), [Renewal Date], "d")), -DATEDIF([Renewal Date], TODAY(), "d")) I am using the formula above and this is correctly showing me the items that are past due and for items that ...
0
votes
2answers
28 views

Adding Column Total

Good Morning; I have a Custom List with a single column recording costs. I want to add the entire column (vertically) to give me a total. So far I have only been able to find advice on adding across ...
0
votes
2answers
35 views

Calculated Formula for Finding three diferent results

I am working on a calculated formula based on my [Tracking Number] column to get the following results: Find "Andrews" return the result "ANDREWS". Find "Bolling" return the result "BOLLING". ...
0
votes
1answer
27 views

Calculating a due date based off two columns

I have an [assessment] and a [reassessment] column and I want to calculate the new reassessment due date. If the [assessment] date is blank then I want the result to be blank. If the [assessment] ...
1
vote
2answers
32 views

Calculating Time Duration

Working within SP, I have two columns Tech Checkin and Tech Checkout with a third column titled Visit Duration. The tech checkin and check out columns will contain a time only and are set as single ...
1
vote
2answers
54 views

IF Statement for SharePoint calculated columns comparing months

I'm Trying to display dates in a calculated column in the format of "DD - DD Month Year" (05-10 August 2018) or as "DD Month - DD Month Year" (30 August - 03 September 2018) if the Months are ...
1
vote
1answer
33 views

Calculated Date formula not updating?

We have a Custom List in SP Online which pertains to parts needing to be inspected. Our list has the following fields: LAST-INSP: a manually entered date showing when the part was last inspected. ...
1
vote
1answer
43 views

Need Assistance Updating an IF/THEN Statement

I have this calculated column formula for stoplights on my SharePoint list and I need just a little help updating/tweaking this particular formula. IF/THEN statements are not my forte. =IF(OR([...
1
vote
1answer
11 views

Date caulculation based on result of drop down column

I am trying to assign a due date based on the outcome of a column. I have a drop down on a list with the choice Complaint or No Complaint called Related to Complaint? I am trying to set a due date ...
1
vote
1answer
43 views

How to get a count of values in field using calculated columns

I have a calculated column and would like to get a count of all the occurrences of "true". So the first row shudl return 3 and the next 2 etc.. Can this be achieved using calculated columns Thanks in ...
0
votes
2answers
16 views

Calculated Forumla - help joining syntax

I'm not sure if it can be done, please note I am not a novice, I am trying to join multiple syntax for a calculated column. I have column 1 as 'state' its values are either California or New York ...
1
vote
1answer
17 views

Help with Calculated Formula

I'm trying to create a calculated field column but its not being accepted. I'm trying to write a syntax for if 'column 1' = 'value1' and 'column 2' does not equal 'value2' then return 'employee name'. ...
1
vote
1answer
27 views

Calculated Column returning Undesired Result in one of over 300 list items

I have a calculated column in a Sharepoint List that is simply adding up 5 other columns with the following formula: =RWDPeriod1+RWDPeriod2+RWDPeriod3+RWDPeriod4+RWDPeriod5 The problem is that ...
5
votes
1answer
60 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!
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 ...
2
votes
2answers
46 views

Calculated column displaying “#Name?”

I'm trying to display KPIs that show how long a list item spend in each status. Column name = Time In 5. =IF(OR(ISBLANK([Date entered 6. ]),ISBLANK([Date Entered 5. ])),"No KPI dates",DATEDIF([Date ...
1
vote
2answers
28 views

Formula of Calculated Column

I want to make a formula to my calculated column : If the DD Rating is High => Add 3 months to the [DD Sign-off Date] If the DD Rating is Medium=> Add 6 months to the [DD Sign-off Date] If the DD ...
3
votes
1answer
98 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 ...
1
vote
2answers
5k views

Calculated field for difference between two times

I have a list with a "Start" field and "End" field. I'm using a calculated field to determine the difference in hours and minutes between the two. Start - 9:00 am End - 10:30 am =TEXT(End-Start,"h:mm")...
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 "...
0
votes
1answer
37 views

How create a SP Calculated column based on date comparison

I am trying to create a calculated column based on a date comparison. This should be a simple if else block condition but its driving me nuts. I have a date column called "Expiration Date" and I am ...
0
votes
1answer
47 views

IF/Then formula producing error “refers to a column that does not exist”

I tried to follow the instructions I found on the site to get the CY month number associated with the 3 letter month column. IF([Month]=JAN,"1",IF([Month]=FEB,"2",IF([Month]=MAR,"3",IF([Month]=APR,"4"...
0
votes
1answer
29 views

How do you assign value to individual choices

Using: SharePoint Online How do you assign value to the following choices Low = 1 Medium = 2 high = 3 Then later on I want to be able to calculate 5 different questions for the total risk rating. ...
1
vote
4answers
11k views

Display year in calculated column in SharePoint

I am creating a column in SharePoint list. This column is calculated. I am using below formula as of now: =""&"INC0000"AutoID This shows results like: INC00001, INC00002, etc. I want to change ...
0
votes
1answer
53 views

How to create formula based on column value

I have a column in my SharePoint list named "Container Content." When adding a record in SharePoint you can choose one of three items in this column, it is a choice column. The three items you are ...
1
vote
1answer
40 views

How to use the ROUNDDOWN action along with an IF statement in a calculated column

I have a calculated column that is using a conditional IF statement to do a calculation.I am using a variation of this in 3 different Calc columns. The problem i have is that my amount are rounding up ...
0
votes
1answer
41 views

IF statement for date calculation

I have been trying 50 different ways to get sharepoint to display a date based on other columns. I tried breaking it down into smaller parts figuring that my IF formulas were too long or sharepoint ...
2
votes
1answer
128 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] ...
0
votes
2answers
52 views

DATEDIFF() Not Calculating Number of Years Correctly

Using 10/1/2008 as the "HireDate" field, the calculated "Number of Years" returns 9 years when it should clearly be 10 years. Using 8/1/1988 as the "HireDate" field, the calculated "Number of ...
-1
votes
1answer
29 views

How to make a column that color codes based on how old items in the list are

I am trying to make a column for my sharepoint list that is color coded based on how old items in the list are(i.e. if items are less than 1 day old there will be a green circle, less than 2 days but ...
0
votes
1answer
31 views

Calulated Column with Multiple IF Statement

Ok, I know I am missing something. What am I doing wrong? Trying to write a calculated column with multiple IF statements. Here is what I have. =IF([Status]="OPEN",[Date Opened]+3,"")IF([Status]="...
1
vote
1answer
53 views

Caculated Column IF statement

Having a hard time finding this question already answered somewhere, so here it is. I am trying to create a calculated column that returns the following result. If "column 1" equals "OPEN", then ...
1
vote
2answers
177 views

SharePoint calculated column based on Choice values of another field

I'm trying to do some automated scoring based on the values of a Choice field. The user can select more than one value. I need to SUM all the points for the various choices made by the user. Column ...
0
votes
2answers
26 views

Need help creating nested IF and LEFT formula in a calculated column

I have the following data in a text column called [Workcenter] 1 SOCS CSS SCXP SCQA SCOO SCPR I need a formula which will create the following results CC CC SCX SCQ SCO SCP I'm trying to ...
0
votes
1answer
22 views

Help with Calculated Field Formula

I need a calculated field formula to compare two dates from two fields - and display the one that is the latest date.
1
vote
2answers
1k views

Calculated column comparing two dates

I can't seem to get my formula working. All I need is to compare 2 date fields: The out of the box [Modified] and a custom [Published Date]. The published date is empty until filled by a workflow. ...
0
votes
2answers
31 views

Calculated Field Attempt not Working

I am in a need to get a calculated field that will accommodate the following: If Opp Type = Extension Renewal or On Contract Growth AND If TnC Change = No AND If Fin Pro = Yes Then set value to Yes ...
0
votes
1answer
32 views

Validation Formula in SharePoint List

I have a Formula and i want to ADD this condition : We can't Choose Closed Status when [Financial impact]<>"Loss",[Breach Nature]<>"Active" This is The Formula where i have to add this ...
1
vote
2answers
784 views

how to update formula of calculated column using PnP in SharePoint Online

I want to update formula of calculated column using PnP or powershell. please note that i am using SharePoint Online. i tried below but its giving me error. please help. PS C:\Windows\system32> ...
0
votes
1answer
23 views

Column validation sharepoint list.Whats wrong with this formula

H All, I have a date i need to validate to make sure the user picks a date not less that todays date. I typed in this formula below ="Review Date"<TODAY() that works for the validation. but ...
1
vote
1answer
60 views

Calculated column between two date fields but if one is blank use another date field

I would like a calculation that gives the number of days between 2 dates (start date & end date) but if end date is blank I would like it to use todays date. How can I set this formula up?
1
vote
1answer
44 views

SharePoint Filters and Custom Views Total Calculations

I have a Date column where users can enter any date they want, I also have separate column that shows the month of that same date column. I am trying to create a filter that will count how many ...
0
votes
2answers
152 views

SharePoint calculated column based on value of another field

I'm trying to do some automated scoring based on the values of various Choice fields: Column name is Project Choice values are Widgets1, Widgets2 and Widgets3 The scoring is based on the choice of ...
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=""{" &" ...
1
vote
1answer
276 views

IF Statement for Calculated Column where answer show negative number as #NUM!

I am creating a calculated formula to tell me Days Past Due and am trying to find a way to change the #NUM! to "ON TARGET". For Example: Item: A Start Date: 8/1/2018 Due Date: 8/16/2018 Days ...
2
votes
2answers
67 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
61 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
127 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 ...