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
1k
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 ...
- 11
0
votes
1
answer
716
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
1
answer
40
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
1
answer
1k
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 ...
- 195
2
votes
1
answer
76
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:
=...
- 23
2
votes
1
answer
2k
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(...
- 37
1
vote
2
answers
1k
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 ...
- 11
1
vote
4
answers
280
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 ...
- 13
0
votes
1
answer
413
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
1
answer
202
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
2
answers
80
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 ...
- 1
0
votes
1
answer
329
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 ...
- 115
0
votes
2
answers
220
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 ...
- 1
0
votes
1
answer
1k
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, "...
- 57
0
votes
1
answer
3k
views
Calculated Column and File Name 'Title' Field
I uploaded more the 2000 images files to an SP document library.
When I checked the 'See Details' under the file name property options, I noticed the actual name of the file appears in the "Name&...
- 115
1
vote
1
answer
307
views
Sharepoint: Creating another view with unique values and sum
I have such a list as per screenshot.
I need to create another view, where first column "Risk Category" will show only unique values (so only 3 items in this case: "Legal risk, "...
0
votes
3
answers
396
views
Document Library Calculated Column
I have a document library in which are a couple thousand image files formatted as such:
CQYr UnqID Title FNme LNme Agency
2020 RR0000 Bus Driver Roger Rabbit Federal
I am trying to create ...
- 115
0
votes
1
answer
201
views
Date Calculation for Leave Entitlement
I am trying to calculate leave entitlement based on the date of joining and current date, at antry date the person has 20 days of leave if the person has completed their 2nd year anniversary by 01 ...
- 51
0
votes
1
answer
47
views
Adding / Creating Calculated Column
I have a document library with a 'Name' column that has files formatted as follows:
CQYR UnqID Title FirstName LastName
2020 RD0000 Teacher Rodger Dodger
Is it possible to ...
- 115
0
votes
2
answers
59
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 ...
- 57
0
votes
1
answer
34
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&...
0
votes
1
answer
25
views
I am trying to create a IF/AND/OR statement in SharePoint, but it's not capturing what I need it to
The formula I have now is:
=IF(AND([Add or Remove]="Add",[RealPage Complete]="No",OR([Add or Remove]="Terminate",[RealPage Complete]="No",[CMP Contact Unmapped]=...
0
votes
2
answers
110
views
calculated field in SPO
I am using SharePoint online. My final goal is to show a calculated value.
So what I am trying is: SUM all values in one column if the ID of the item is the same. It is a list of documentations and ...
1
vote
1
answer
55
views
Help with nested If statements that format a phone number based on country
I am trying to create a calculated column with nested if statements. Due to my users not wanting to format the phone number correctly I am creating this new column to take the phone number in the ...
- 23
1
vote
3
answers
343
views
Calculated text column in SharePoint Online results in a ‘0’
I would like either entry in the Status column to return a blank text value. Right now "Assigned" is resulting in a "0".
=IF(Status="Discontinued","",(IF(Status=...
- 11
0
votes
1
answer
669
views
I am working on a microsoft flow which updates a column on the basis of another column
Here is the screenshot of the source and destination:
1. I am working on a flow which updates a column SME Comment CK on the basis of another column SME Comment. These columns are multiline text. but ...
- 79
1
vote
1
answer
55
views
Number formatting
I have a two choice columns that display same three choices: 0, 3, 9
Column 1 has a value of 9
Column 2 has a value of 3
I want to add 9 and 3 on a new column.
Something so simple is giving me issues?
...
- 101
1
vote
1
answer
37
views
Sharepoint list columns
Is it possible to create a SharePoint column based on choice (definition) that when selected populates the rating on another column?
I will need to add sum of all scores:
Thanks a million!
- 101
0
votes
1
answer
1k
views
SharePoint List Autoincrement number field?
I need a autoincrement number using number field ( I set default value is 5000 once add the items it will increase one by one) in SharePoint. Can anyone help me using formula.
- 21
0
votes
1
answer
32
views
Calcluated Column multiple IF with SEARCH statement coming up as #Name?
I want to search each cell in a column for a certain pharse. I figure I should use IF statements but since the following text can be anything I need to incorporate a SEARCH statement as well. Like at ...
- 13
0
votes
1
answer
63
views
Calculated Column - Dynamic project end date
I am building a project list that has the following columns:
The "Projected End Date" column is calculated by taking the "Start Date" and adding the number of of "Quoted Days&...
- 57
0
votes
1
answer
558
views
Calculate the difference between two dates in hours and minutes
In SP online in a list, I have a formula that calculates the difference between two dates and outputs the result in Days+Hours+Min.
I was wondering if it's possible to obtain the results only in Hours ...
- 3
0
votes
1
answer
430
views
Calculating business days between dates and allowing a negative number
I have some working code that calculates business days between two dates:
=(DATEDIF([Date Submitted],[Start Date],"D"))-INT(DATEDIF([Date Submitted],[Start Date],"D")/7)*2-IF(...
- 1
0
votes
1
answer
168
views
SharePoint Calculated Yes/No Column
I've tried to create a calculated column in a SharePoint list that looks for a word in the title of the list entry and then returns yes or no based on if that word is included in the title or not.
=IF(...
0
votes
2
answers
35
views
Calculated Filed
I have created a calculated filed to convert a date field to another format, the issue I have is the calculated filed populate data for empty fields as well. Is there anyway it can only populate data ...
- 547
1
vote
1
answer
5k
views
How to format calculated column number in Flow
I am using a calculated column (number) in a SharePoint list, and putting the value into an email using a Power Automate Flow. The value will always be an integer.
When the value is passed through to ...
- 115
0
votes
1
answer
24
views
Calculation in Calculated Column not Working
I am unable to determine why SP keeps kicking back on error on the following.
This is a calculated column that is reading the contents of a CertQualTitle column and applying/returning the appropriate '...
- 115
0
votes
2
answers
63
views
Calculated Column Question Blank Dates
I have been stumped on this particular task for weeks. Any help would be greatly appreciated.
I have three columns I am working with: Date Admitted to Hospital (date column), Date Discharged (date ...
- 71
1
vote
1
answer
51
views
Mulitple IF Statements based on other criteria
I have this and it works on its own:
=IF(ISBLANK([Basic Finish]),"Past Due",IF([FCM 4 Expected Date]>[Basic Finish],"On Time",TEXT([FCM 4 Expected Date]-[Basic Finish],"h:m:...
- 11
1
vote
3
answers
442
views
Blank a column if a condition is met
I have a established a custom list with 5 columns for testing.
Title column
Date columns (Date1 & Date2)
Calculated column to count days between the 2 dates (Total)
Calculated column to count how ...
- 39
0
votes
1
answer
27
views
Calculated field in Sharepoint Lists when some of the text fields are empty
I am trying to create a calculated field that produces a person's full name in the following format:
"1stLastname 2ndLastName, FName MName".
I am using the following formula, but keep ...
GS CrimDefense
0
votes
1
answer
415
views
Calculated Column to Get Totals & Percentages
So I have columns set up like so:
Entity Region Quarters Year Month #ofOps #ofMisops %ofMisops
Company A East Q1 2020 Jan 1 1 100%
Company A ...
- 1
1
vote
1
answer
34
views
Nesting IF Statements in SharePoint Calculated Column
Uhh!. I have 3 statements that individually return my values. Now, I need to put these together into one formula. Here are my statements that need combined into one calculated column. Thank you.
1st ...
- 11
0
votes
1
answer
40
views
How to get a particular value in the calculated column
I am trying to implement the following code in a calculated column named [Valid] in a document library in modern SharePoint online.
=IF([Valid Until]-TODAY()>=0,"Valid","Expired"...
- 1
0
votes
1
answer
438
views
Button on SPO Page that copies the URL of an item in a list view web part with a checkmark next to it
Basically, if ONE document in a document library LVWP is checked, a button should become active. When clicked, the button will copy that document's absolute encoded URL to the clipboard. It doesn't ...
- 123
0
votes
1
answer
147
views
Priority Due Date column sharepoint online
I need to setup a Due Date calculated column on an SPO list and whilst I've managed to work out the formula for days, I cant quite work out hours?!
I have SLA priorities on my list as per the below:
5=...
- 439
0
votes
1
answer
498
views
Calculated Column with TODAY() issue
Using the following:
"If Authorization Date is null, calculate the difference between Receive Date and today; If Authorization Date is not null, calculate the difference between Receive Date and ...
0
votes
1
answer
73
views
My Sharepoint formula not working
=IF([Sett. Start]<=Today,"Visit Due",IF(ISBLANK([Visit Notification No.]),"Visit Not Due","Visit Done"))
I am using above formula in SharePoint List but it is working ...
0
votes
3
answers
159
views
=IF(ISBLANK([Last Update]),"",TEXT(NOW(),"dd/mm/yyyy")-[Last Update]) is retruning negative numbers
I have the following formula inside a calculated column:-
=IF(ISBLANK([Last Update]),"",TEXT(NOW(),"dd/mm/yyyy")-[Last Update])
but today is 27/08/2020, and on some cases where ...
0
votes
1
answer
895
views
Calculated Columns - Comparing multiple columns with IF statement
I am fighting with SharePoint calculated columns and trying to compare multiple columns.
The SharePoint list has the following columns:
Completed Date - Date
Completed - Yes/No
Due Date - Date
...
- 57