Questions tagged [calculated-column-formula]

Question related to the Calculated Column

Filter by
Sorted by
Tagged with
1
vote
3answers
21 views

Calculated Column Yes/No based off 3 Yes/No columns

If column A, B & C all show yes then column D should also be yes If column A, B or C shows no then column D should be no Can somebody help me write a formula for this to use in a SharePoint ...
1
vote
1answer
19 views

SharePoint calculated column using AND

I'm wanting to create a column that will provide a yes or no answer depending on some other conditions. I thought I found something that was going to work but it didn't. So basically Annual pack days ...
1
vote
1answer
28 views

Using date commands in calculated fields

I am following this article: Examples of common formulas in lists A simple sample is this: =DATEDIF([Dato1],[Dato2],"d") However when I save the column I get following error: Sorry, ...
1
vote
2answers
34 views

SharePoint list: Conditional required field - Hhow switch off

In my SharePoint list there are two columns: A and B (both of them are required). Scenario: If user will input value in column A, he don't need fill column B. And reverse, if the user enters the value ...
1
vote
1answer
23 views

SharePoint Calculated Column showing #NUM!. One of the Column needs to be in Completed Status

I have a calculated column that will get the date difference between the two columns. Conditions: Difference between Completed Date and Created Date. Weekend should not be counted. If Completed Date ...
0
votes
1answer
11 views

How to incorporate sorting in calculated column with background and text color formatting to use as a Grouping field

Here is the calculated formula for the field: =IF([AU-R CIR]="7. ERROR","<div style="""&"text-align:center; color: red; background: #ffffff"&"&...
0
votes
2answers
28 views

List Validation Formula not working

I need your help to fix my issue, basically what I want is when users select B from choice filed, the next column become mandatory. Below formula doesn't work: =IF([A]="B", [TEST] = " &...
1
vote
2answers
11 views

Calculated column for differences between two column dates with blank check if blank use another column for difference - Sharepoint list

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: =DATEDIF(...
1
vote
1answer
26 views

Convert Excel Formula to Sharepoint Calculated formula

I'm having difficulty converting this Excel Formula to Sharepoint calculated formula. Hoping someone can help. =IF(OR($F13>=TODAY(),ISBLANK($F13)),0,IF(ISBLANK($G13),NETWORKDAYS($F13,TODAY(),...
0
votes
2answers
15 views

Calcualted field for created date -today not working

I am trying to make a field to show how many days it has been since this item was created. =IF(ISBLANK([Created]),"",TEXT(NOW(),"mm/dd/yyyy")-[Created]) Above is the formula used ...
1
vote
1answer
13 views

How can I set a special format in a column or leave the field blank

I have a column that is called APC Standard. In this column I want to set the following format APCxxxx (APC + 4 numbers). I also want to have the possibility to leave the column blank. I have already ...
2
votes
1answer
41 views

Calculated field for date columns, if date1 has a date use it, else if date2 has a date use it, else use date3

I am working on building a calculated field using 3 date columns on a list and I am having difficulties due to being 3 dates and If/Else. Basically it should calculate a date based on if date1 is not ...
1
vote
1answer
17 views

Calculated column, if box is checked then date+6 month if not date+3 month

I have a SharePoint list with 3 fields: Date: the user enter the info Prolongation: Checkbox Second date: this is a calculated column. This is what I want: if the "prolongation" box is ...
1
vote
2answers
19 views

How to total the number of selected columns that have string data?

Within a SharePoint Online list, if I have a record that has 6 columns like so... ID: #id of this record Title: "my first test" Step 1: "apple" Step 2: "grape" Step 3: ...
0
votes
1answer
28 views

Calculated column for IF not working

I have created a calculated column to see if the target has been met. I have tried different combinations of IF formulas. Am I missing something? Thank you =IF([Response date]=0,"Not met",...
0
votes
1answer
12 views

Filter a view based on an array of values

I'm trying to create a view to temporarily expose the various documents that are missing a specific column value. I thought I could create a calculated column with a simple clause that states =IF([...
1
vote
3answers
33 views

Impact on Performance of Calculated Columns

I have an SP Library. I created several calculated columns in the Library (using LEFT, RIGHT, MID) to break-up the content of the 'Name' column. Everything works fine; however, I was having a ...
0
votes
1answer
27 views

IF OR / DATEIF calculated column

I have a calculated column that returns days open for job listings. =DATEDIF([Requisition Open Date],TodayDate,"D")-IF(WEEKDAY(TodayDate)=7,FLOOR((DATEDIF([Requisition Open Date],TodayDate,&...
1
vote
1answer
7 views

Column 1 and 2 are equal or the same then column 3 defaults to yes

I want to change a column to yes if two other columns are equal, so same title and report week. So if report week column and title are the same, then duplicate entry is yes. =IF[Report week][Title]<...
1
vote
2answers
34 views

SharePoint List calculated column based on three different columns values

On one side, there are four dropdown columns (Scope, Time, Resources and Budget) that can contain only one the following values: Green, Yellow and Red. On the other side, I want to make "Project ...
0
votes
1answer
54 views

find text in calculated field

All, i am trying to extract part of a string in a calculated field. The calculated field is looking in the title field, of an item in the library, for the word "DIS". The formula I am using ...
0
votes
2answers
21 views

What is wrong with my calculated column formula?

I have a SharePoint list which contains a column which displays a Custom Item ID created by concatenating the ID field and the last 2 digits of the year (like this 01-21). In some circumstances it is ...
0
votes
1answer
37 views

Calculated column with IF(AND(OR statement to derive a value

I would like to use an IF(OR(AND function (or a function that works) to obtain an answer of 1 or -100 within a SharePoint list. My formula works in Excel but not in SharePoint. =IF(OR(AND(Q10="NO&...
0
votes
1answer
22 views

The Function Sum(IF(AND())) Statement is not calculating correctly

If my [STARTDate] = 12/24/2020 AND [ENDDATE] =1/4/2021 It gives me a total of 3 days. SUM( (IF(AND(DATEVALUE("12/24/2020") >= [STARTDATE],DATEVALUE("12/24/2020") <= [...
0
votes
1answer
24 views

Calculate SharePoint Column for unique expiry dates

I am building document library on SharePoint Online to store employee training certificates that can monitor their issued and expiry dates. The validity period for each certificate is different ...
0
votes
1answer
14 views

2 Lists - Linking together, having trouble

Company description - Widget Rental Services 2 Lists - List 1 = Customer Order/Scheduler (Non Calendar) List 2 = Inventory/Asset List When inputting customer order on list one, we would like to book ...
0
votes
1answer
16 views

3 IF Statements

I have a calculated column which should return DepA, DepB or DepC according to the multiple IF statement which I cannot work out correctly!!! I have a DateRequest which is entered by the user. I then ...
0
votes
1answer
21 views

Multiple IF Statement

I have a column ExpireDate and am trying to return an end of the month date based on the created date is less or equal than the 10th of the month will return the last day of the month, if is greater ...
0
votes
2answers
41 views

Sharepoint Calculated Column - Get last instance of substring

I need to get the last value after , from the string. e.g. 10%,90%,12% It should return 12% =RIGHT([Payment Term],LEN([Payment Term])-INT(FIND(",",[Payment Term]))) I used the formula above ...
0
votes
0answers
23 views

Substract a calculated time from antoher calculated column

I have a SharePoint list in a site as below: Where below are columns: Wer: Person field Zeit_an & Zeit_ab: Date-Field with time included Pause: calculated Column which calculates if its Friday ...
1
vote
2answers
13 views

Calculated column using 4 different columns to derive an “approve” or “Decline” answer

I would like to use an IF function to obtain an answer to "Approve" or "Decline" within a SharePoint list. My formula works in Excel but not in SharePoint. I have 4 SharePoint ...
1
vote
0answers
17 views

Updating calculated field formula via CSOM with non-english regional settings fails with “The formula contains a syntax error or is not supported.”

In our current project we programmatically provision site collections, site columns and content types using CSOM/PnP. During this provisioning process the initial locale id is switched from 1033 to ...
0
votes
1answer
28 views

SharePoint calculated fields - get the first date from 4 columns

I need a little bit of help with calculated fields in SharePoint. I have 4 dates (it can be more in the future). All 4 dates are due dates. So lets say I have 4 fields. [Car Damage repair] [Car ...
1
vote
1answer
16 views

Nested calculated column formulas

I need some help with a calculated column. My formula looks like this, and so far it is working. =IF(Column1="Sweden";[Column2];Column3) But The data shown if Column1 = Sweden, contains a &...
2
votes
2answers
44 views

Calculate Column Based On Self (Circular Reference Workaround)

I have a date and time data type calculated column named "Date of Review" with the following formula: =IF([Status]<>"Select",NOW(),"") Formula explained, if the ...
0
votes
0answers
15 views

Calculated (calculation based on other columns) IF OR ISBLANK AND Sharepoint Lists error formula

Can anyone help? I can't work out why this is erroring. this section =IF(OR(ISBLANK[Tech £]),"", at the start in bold makes it error. =IF(OR(ISBLANK([Tech £])),"",IF(AND([Tech Pay ...
1
vote
1answer
33 views

My Calculated Field with a nested IF(OR) statement is returning error

I am trying to input an email address into a calculated field with the following formula (I have replaced proprietary information with generic information in this formula): =IF(OR([Customer Name]=&...
1
vote
1answer
36 views

Calculated Column 21 business days from start date

I have a field that is called Transmit Date, I need to generate a calculated column 21 business days from the date of this field. I have seen some other posts that fall within a week. I am curious how ...
0
votes
1answer
45 views

Remove characters from Text within a list - SharePoint 2016 onprem

I am trying to remove certain characters or only display certain characters within a list. The source field is called [Issue Status] and created a calculated column with the following: =LEFT([Issue ...
2
votes
1answer
34 views

Help combining SharePoint formula statements - DATEIF and ISERROR

I am running a SharePoint formula that calculates the length of time (in yrs, months, and days) before a project expires. In the "Will Expire" column the following formula is running: =...
0
votes
2answers
33 views

Hierarchy of Formulas In Calculated Columns

Is there an established hierarchy for formulas in calculated columns? Long story short our IT team got lazy and didn't complete user profiles. In order to get a proper name from a user field I'm using ...
2
votes
1answer
25 views

Create a view that shows entries only if they are 120 days from a date column

I have a list that tracks users loss date from the organization. This date is assigned from they day the arrive and are entered into the list (loss date). I am trying to create a view that would ...
1
vote
2answers
29 views

How to Create Calculate column using other Lists

I have a DB on my SharePoint site using several Lists. For one of the lists I need to count Total, Pending and Completed records from the other tables. There's one list for each department and on it ...
0
votes
1answer
70 views

Sharepoint list: How can I calculate difference between two dates without Fridays and Saturdays

I need a formula that can calculate difference between 2 dates without calculating Fridays and Saturdays. I need to use this formula to create a calculated column in list. Thank you
0
votes
2answers
28 views

I need it to show Yes automatically when the status is completed

I need it to show YES automatically when the status is completed and remaining as NO if is still in progress. Please share a code to make this happen. See the images attached. I have also tried ...
0
votes
1answer
24 views

Calculated Column Modification

I've got the following file name convention for images in an SP document library - CQYr UnqID Qual Name 2020 RR0000 Bus Driver Roger Rabbit I am using the following in a calculate column in ...
0
votes
2answers
38 views

Create calculated column that increases daily and resets on value of another column

Let's say I have two columns in a SharePoint list: [Invoice Age] - text (20, 23, 25) [Payment Status] - text (Not Paid, Paid) I'd like to create a calculated column [Invoice Age] that will increase by ...
0
votes
1answer
58 views

get count of duplicate values in a calculated column in sharepoint list

I have a sharepoint list with data as below. ID EN DE FR Val 1 EN1 DE1 FR1 1 1 EN1 DE1 FR1 1 1 EN2 DE2 FR2 2 1 EN2 DE2 FR2 2 1 EN2 DE2 FR2 2 1 EN3 DE3 FR3 3 ...
0
votes
2answers
30 views

Calculated Colum - Ordering nested IF statements

I don't know if what I am trying to do is possible, but this is the theory. I have a 3 columns in SharePoint Project State - TEXT Days Overrun - NUMBER Actual Finish Date - DATE The "Project ...
0
votes
1answer
23 views

How to use Calculated column to check if time is between 2 times

If I have an end time of "12.30 PM", how do I use calculated column to check if this is between lunch time from 12pm-1pm. I want to display "Yes" if it falls between and "No&...

1
2 3 4 5
13