Questions tagged [calculated-column]

Calculated columns are the SharePoint equivalent of Excel formulas. They allow you to 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
13 views

Working with the file name

I am fairly new to SharePoint and also working with a German version, so please excuse and correct any wrong terminology. On SP2016, I have an image library and have added a calculated column. I am ...
1
vote
1answer
7 views

Multiple conditions in in Sharepoint calculated column

I am a bit new to SharePoint lists. I am trying to create a calculated column with multiple conditions as below. So the priority needs to be automatically calculated based on other 2 columns Impact ...
1
vote
1answer
18 views

Calculated column from period

My sheet contais 3 columns - date, time period (choices: one yer period, two years period and half year period) and I would like to create rule for the third column which suppose to calculate the ...
1
vote
1answer
27 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
31 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
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
13 views

Sharepoint 365 Document Library Calculated Column Searching for Text

I am trying to search in the files names for HC, so I can have a Yes / No output to be able to filter on it, since MS does not have a does not contain option in filter. Names of the File: SLS802 2021 ...
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"&"&...
1
vote
2answers
13 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(...
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
42 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
18 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
21 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: ...
1
vote
1answer
43 views

JSON formatted list view does not display a calculated column value

I am using JSON view formatting to display list items in tiles. I use a calculated column to display a date in the "23 March 2021" format and output it as a single line of text. However, ...
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
15 views

Item ageing calculation in sharepoint list

Need help to formulate a column to calculate the line item ageing based on Request Status. If the request status moves to completed, it should stop/freeze the result. If the request is in any other ...
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
2answers
26 views

Calculated column based on what's in field of another column in same row

I'm wondering if there's a way to create a calculated column based on what's in a multple-choice column? For example, something like the pseudocode below: If [column 1] is in ["item 1", &...
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
28 views

How to compare dates in SharePoint List “A” to another Date in List “B”

So, I would like to Compare if any of my Dates from List "A" is between my StartDate and EndDate from List "B". I am new to sharepoint and any Article can show step by step would ...
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
19 views

Is it possible to use a flow to move an item from an active list to archive list based on values in certain fields and then…?

Good afternoon everyone, I have 2 SP Lists, The first is the Active List, within this list (several columns) i have one column "Status" that is a choice [New, Assigned, CM Active, Completed] ...
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
1answer
79 views

Extract description rather than URL in calculated column to use it with another url

Let's say the first column is UID having number 12345. You will be directed to the URL after you click on 12345. The second column I am trying to create is like a URL to some website followed by 12345....
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 &...
1
vote
1answer
93 views

Microsoft Lists - Date formula to exclude weekends

I am using Microsoft Lists for our work schedule and I would like the "Due Date" column to be calculated based off the "Prepare Date" and "Number of Days" columns ...
0
votes
1answer
28 views

Extract the Name of the uploaded file into another columns with a flow

I want to extract the name of the file to another 6 columns. For example, you want to put the "D" in the file name in the first column and the word "Berlin" in the second column, ...
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]=&...
0
votes
1answer
46 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: =...
2
votes
1answer
62 views

Calculated Column Greater than or equal to with nested if

Can you please help with this formula. =IF([Result2]<=3,"Low",IF(AND([Result2]>=4, [Result2]<=6),"Low Med",IF(AND([Result2]>=7,[Result2]<=12),"Medium",IF(...
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 ...
1
vote
4answers
66 views

SharePoint Column - Conditional Formatting

I basically have a calculated column that shows the date 1/29/1900 if there are some empty values in the other columns. Is there a way to use JSON formatting to format it so that if the column ...
0
votes
0answers
44 views

How to get a Power Automate flow to stop updating after 1 item in a SharePoint list

New to SharePoint and Microsoft flow/Power Automate. I have made a flow that copies the sum of a calculated column (Summa) into a currency column that can display a sum (Total). The reason of course ...
0
votes
1answer
57 views

Count occurrences of a particular value in sharepoint folder

Trying to either use FLOW or calculated column in sharepoint to try and achieve the following. Appreciate anyones help on this. God Bless Janan
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
30 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
34 views

Unable to Reference 'Name' Column for SP Calculate Column

I created an SP document library and moved 2700 .jpg files into it. I am trying to create a Calculated column that looks at the 'Name' column in the library and extracts characters 1 - 4 the first new ...
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
93 views

SharePoint calculated column, converting time into decimal then dealing with greater than 24 hours using IF

I have a SharePoint list that has the following columns: Start Time End Time Employee Count Total Hours Total Hours (Decimal) The "Start Time" and "End Time" are DATE format, "...

1
2 3 4 5
22