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.
1,228
questions
1
vote
1
answer
32
views
IF statements SharePoint 2003
I am using SharePoint 2003.
Only 7 If statements on SharePoint:
1,2,3 < green
4,5,6 yellow
8,9,10 amber
15 > red
=IF(RAG<=3,"Green",IF(RAG=4,"Yellow",IF(RAG=5,"...
- 13
0
votes
2
answers
173
views
Not able to use renamed Title column in a calculated field
I'm trying to set a formula that uses the Title field which was renamed. But when I save the calculated field the SPO shows an error telling me that I'm using a field that does not exist (!!!).
The [...
- 287
0
votes
1
answer
1k
views
Calculate Days, Hours and minutes between two different dates and times
I have a SharePoint list with two date columns. I want to create calculated column which gives the number of days, hours and minutes between these two dates.
I have looked at all possible threads, but ...
0
votes
1
answer
31
views
SharePoint 2013 Calculated column issue -Comparing dates
Hope you can help me on this issue.
I created a calculated column with the following formula to auto calculate the "Fiscal Quarter" based on the value in the "Date Of Review" ...
- 195
1
vote
1
answer
622
views
Check if a time is between two other times including minutes
I have a PowerApps form which contains an adjustable "Submission Date" field. Here is a screenshot to show what I mean:
I am trying to come up with a formula for a calculated column in ...
- 11
0
votes
2
answers
27
views
Additional option in Column Validation
I have a a validation that only accepts a specific format of 6numbers followed by a dash and further 6 numbers as below:
=IF(ISBLANK(FAQ),TRUE,AND(LEN(FAQ)=13,MID(FAQ,7,1)="-",ISNUMBER(MID(...
- 51
0
votes
1
answer
99
views
Calculate business days between start date and end date
BLUF: I would like to create a calculated column in SharePoint that looks at the [Start Date] and [Provisioned Date] and know how many business days it took between the two.
I am very new to ...
- 1
1
vote
1
answer
24
views
SharePoint Online Calculated Formula
I am trying to create a formula that will auto-populate a column based on the input from two other columns. The auto population would need to check the boxes of the appropriate items that apply based ...
1
vote
1
answer
300
views
Calculated Column Formula not Working
I'm trying to create a calculated field with the following formula, but when I try to save the column with this formula it returns a syntax error.
What's wrong with the formula?
Formula:
=CONCATENATE(...
0
votes
1
answer
22
views
I am using an IF function for a column but its giving me an error and I am not sure what the problem is. Can someone please help?
My formula:
=IF(AND([Column 1]="Low",[Column 2]="Low"),
"No",
IF(AND([Column 1]="Low",[Column 2]="Medium"),"No",
IF(AND([Column 1]="...
1
vote
2
answers
559
views
Sharepoint List 2016 - Replace text
How can I replace the space in between a file name in a calculated column in SharePoint list 2016?
Example I have a file name that contains ABC 123 and I want a calculated column to return the value ...
- 11
0
votes
1
answer
27
views
Calculated Column Assistance Needed
I have an expiration date column, and I need to mark the date red yellow green depending on:
if the exp date is within 30 days from today = red
if the exp date is within 60 to 90 from today = yellow
...
- 1
1
vote
2
answers
46
views
Multiple IF statement in one calculated column
I have tried everything, can someone help me with this?
=IF(ISBLANK([Date1]),DATEDIF([Created],[Date2],"d"),IF(NOT(ISBLANK([Date1),DATEDIF([Date1],[Date2],"d"))))
It keeps saying ...
0
votes
1
answer
38
views
Please Help with Calculated Column
Here is what I'm trying to do:
I have 3 columns, that if they all have certain values, I increase the value of calculated column by 1. So if the value of the calculated column =3, then I want the ...
1
vote
2
answers
150
views
IF Statement in a SP Library Calculated Column
I have an SP Library that has a 'Name' column. The names of the documents are formatted as follows:
2021 CT0000 Driver Charlie Tuna 1_1_2021.jpg
2021 CT0000 Driver Capatain ...
1
vote
1
answer
488
views
How to include calculated column in "New item" in Sharepoint list?
I have 2 calculated columns Urgency and Priority which I need to show in New item in SharePoint which will help the users in knowing/understanding them before they submit the request.
Also is there ...
- 11
1
vote
1
answer
182
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 ...
- 11
1
vote
1
answer
4k
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 ...
- 11
1
vote
1
answer
23
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 ...
- 11
1
vote
1
answer
60
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 ...
- 11
1
vote
1
answer
112
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, ...
- 293
1
vote
2
answers
173
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
1
answer
35
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
1
answer
14
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
1
vote
2
answers
194
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(...
- 13
0
votes
2
answers
19
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 ...
- 41
1
vote
1
answer
16
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 ...
- 11
2
votes
1
answer
580
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 ...
- 21
1
vote
1
answer
372
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 ...
- 13
1
vote
2
answers
69
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,329
1
vote
1
answer
594
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, ...
- 21
0
votes
1
answer
72
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
1
vote
3
answers
418
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 ...
- 115
0
votes
1
answer
550
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
1
answer
783
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]<...
- 11
1
vote
2
answers
772
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 ...
- 13
0
votes
1
answer
608
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 ...
- 11
0
votes
2
answers
70
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 ...
- 765
0
votes
2
answers
51
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", &...
- 101
0
votes
1
answer
2k
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&...
- 13
0
votes
1
answer
26
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") <= [...
- 105
0
votes
1
answer
431
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
1
answer
777
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 ...
- 105
0
votes
1
answer
26
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 ...
- 51
0
votes
1
answer
828
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
2
answers
331
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 ...
- 23
1
vote
2
answers
23
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 ...
- 13
1
vote
1
answer
280
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
1
answer
33
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 ...
- 121
1
vote
1
answer
63
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 &...
- 13