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,224
questions
1
vote
1
answer
654
views
SharePoint list calculated column to sum values from another column
I am trying to create a calculated columns in a SP list to show a running total of data from other columns. I am doing this in excel and it works exactly as I need it to, but I cannot find anything ...
0
votes
0
answers
61
views
SharePoint list running total column using data from a different column in the same list
We have an excel sheet that I am trying to bring into a SP list. The sheet tracks total submissions, total "passing" and total "failing". This number is cumulative number, so ...
- 1
1
vote
1
answer
87
views
Formula to convert Julian to Calendar date
I have created a calculated column with which i want to convert julian date (from another column) to calendar
Does anyone know a formula to do that? I've searched the web but I can only find from ...
- 11
1
vote
1
answer
51
views
Help with Calculated Column conditional formula with OR
I am trying to categorize a SharePoint list of projects into colored approval tracks based on project type using a calculated column. So, depending on the choice from the Project Type column, the ...
- 23
1
vote
0
answers
465
views
SPO Calculated Column Not Updating When Item is updated or when Column Formula is updated (by power automate)
I have a calculated column in SharePoint.
It is dependent on non-calculated columns.
So for instance, my calculated column is "past deadline?" and its formula is:
=IF([run]="yes",...
1
vote
1
answer
43
views
How to use an IF statement on a Time/Date column to automatically populate a new column?
On my list, I have a Time/Date column named 'ActivityDate'.
The format of this column is "mm/dd/yyyy hh:mm AM/PM"
I am trying to set up a new column called 'Result'.
If the time on '...
- 11
1
vote
2
answers
16
views
Calculated Column IF MsLists
I'm trying to move one of our old spreadsheets onto MS Lists. There is a formula in there which is:
=IF(AND(E2=0),"0",IF(AND(E2>=6.5,E2<=8),1,2))
I've tried a few variations through ...
- 11
1
vote
1
answer
11
views
Getting the results of two or more columns to change another calculated column
I have searched and searched the web for the answer but nothing I try seems to work in these SharePoint calculated columns, basically, I have a column called Email Sign Off and another called ...
0
votes
0
answers
13
views
Create a calculated Column in SharePoint. using If then statements to search cells for answer, if empty keep searching
I need to Create a calculated column called “region” using an if then statement as the algorithm to search for an entered cell out of the 5 regions, if it finds it, insert it in the new calculated ...
0
votes
1
answer
85
views
Use Metadata field in formula
It seems that SharePoint didn't allow Managed Metadata columns to be used in calculated fields something like a decade ago.
Is there a way to add metadata field/column in a calculated column's formula ...
- 121
1
vote
2
answers
112
views
SharePoint list calculated column 3 validations for IF statement
I have a SP list that contains software installation data. There is a calculated column that calculates price. There is a column called Installation Date which has 2 choices either Before Sep 30, 2020 ...
- 13
0
votes
0
answers
20
views
Can I display Modified Date for documents only (not folders) in a Document Library view?
I have a document library in a SharePoint on premises site. I want to display the modified date of documents in the library, but folders show the modification date of the folder itself, not the most ...
- 135
1
vote
1
answer
55
views
Sharepoint List - Calculating weekdays between two dates ignoring blanks (Project Registration Date to Gate 0)
Hi I have a calculated column that counts me no. of weekdays
=(DATEDIF([Registration],[Gate 0],"D"))-INT(DATEDIF([Registration],[Gate 0],"D")/7)*2-IF((WEEKDAY([Gate 0])-WEEKDAY([...
0
votes
1
answer
15
views
Years of Service with IFBlank but what if date is in future
I have a field that calculates "Years of Service" and calculated to either TODAY for a active employee OR to the termination date if terminated. However, on the list dashboard if the start ...
- 5
1
vote
1
answer
75
views
Calculated column hyperlink generated not displaying text
I have a SharePoint on-premises calendar list created. need a hyperlink column in event view form to download ics file from a hyperlink.
The download link gets created and works fine but i need to ...
- 13
0
votes
1
answer
19
views
Calcualted column is returning "#Name?"
I have the following formula for a single line of text Calculated column named ShowInApp:
IF(OR(Status="Inactive",ISBLANK(Status)),"Hide",IF(FIND("Pending",[Category]),&...
1
vote
1
answer
37
views
Calculated Columns in SharePoint Online
Can a calculated column look for a field in a separate list in another site collection or is it just limited to the same site collection in SharePoint online?
- 23
1
vote
2
answers
349
views
Adding Calculated Column to List to sum values in columns while ignoring blanks
I have a questionnaire that populates a SharePoint list. There are a number of questions that return answers as picked from a choice column.
I have a calculated column that I'm trying to get to return ...
- 13
1
vote
3
answers
240
views
Calculated column to calculate approved status workflow
How to use calculated column to calculate approved status workflow?
I tried this but it doesn't work.
IF([Approve status]="Pendding", 1, 0)
- 23
1
vote
1
answer
35
views
Create column that is filled automatically (depending on another columns)
I am working with a SharePoint list that contains information about new employees. One column represents the StartDate, another column the EndDate (according the contract).
In my company we have two ...
- 15
0
votes
1
answer
77
views
Calculate years of seniority on termination date OR if blank Today's date
I would like a calculation that gives the number of years between 2 dates (Hire Date & Termination Date) but if Termination Date is blank I would like it to use today's date to calculate how many ...
- 5
0
votes
1
answer
244
views
SharePoint List - How to determine percentage complete based on completed columns?
I have 30 columns, most of them using Yes/No, but also some text. Is there a way to have a Progress column that will automatically calculate how many of the columns have been completed and determine ...
- 1
1
vote
1
answer
98
views
Average Ignoring Blank Columns
Really struggling with formula on a calculated column on my SharePoint list for some reason!
I am trying to get the average of 3 columns sometimes one will be blank some times there will be no blanks.
...
- 13
1
vote
1
answer
59
views
calculate the sum of months from a fiscal year horizontally from one list to a summary list
List 1- Food
Jan
Feb
Mar
Total
1000
1000
1000
3000
sum
sum
sum
sum
This list is a category and lists 2-Clothes, 3-Water, 4-Internet, 5-Misc are the same I need the sum of the columns of this list ...
0
votes
1
answer
57
views
Calculated column not working properly
I have a calculated column which is not working as intended. There is an 'if' condition which checks the value from a date column called 'Modified' and it sets the calculated column to 90 days after ...
- 103
0
votes
2
answers
195
views
SharePoint Calculated Column Not Posting Date Value
I have created a task list. I created an extra column that I want to calculate the value for. When the "Completed" checkbox for the task item is checked, I want to set the value of the ...
- 113
0
votes
1
answer
184
views
How to write multiple conditional formula (39 conditions) for 7 columns in Sharepoint
first, I must thank you all for the tips for new people who want to learn more on SharePoint to optimize their business.
I'm trying to find a solution to write expanded conditional formula according ...
1
vote
1
answer
25
views
CALCULATED column issue
Scenario:
Need to get difference between two dates (Start Date, End Date) in months, and if End Date is blank, then display "Open".
I am trying:
=IF(AND(ISBLANK[End Date], " ", (...
0
votes
0
answers
118
views
calculate difference between two dates in sharepoint, show days, month, years
How to convert this formula from excel to use in SharePoint: to show age in day, month and year?
Current formula:
=IF(DATEDIF(TODAY(),[birth],"y")=0,"",DATEDIF(TODAY(),[birth],&...
- 1
0
votes
0
answers
15
views
Add a day "0" to the Date Picker (ie 0/6/22)
I have read several other posts on this topic but haven't quite been able to get what I want to happen without going through a complicated workaround.
Here's the problem: I need to display the date ...
- 3
1
vote
1
answer
66
views
Using nested IF and IF(AND) statements to display different info in calculated columns
I'm trying to display some text in a calculated column depending on whether or not the input of the other columns is blank.
The columns are single-line text: [Creator] and [Organisation].
The idea is ...
- 13
2
votes
2
answers
28
views
Chronological grouping of dates formatted as MMM YYYY in Sharepoint
I formatted my list to group by dates in a format of MMM YYYY in an ascending order. The formula I used was:
=TEXT([Start date],"mmm yyyy")
However, now SharePoint groups the dates using ...
- 41
1
vote
1
answer
166
views
Calculated column to show negative number as zero
I have the below formula in a calculated column, which in some instances will return the number as a negative number (eg. -1234) how do get the formula to show the negative number as 0.
=IF(ISBLANK([...
0
votes
1
answer
214
views
Need to create an hyperlink in a calculated field
We are using SharePoint 2019 On premises.
I have a column called "Email" that give the email address of the connected user, but in plain text.
I would like in another column to make this ...
0
votes
0
answers
23
views
SharePoint 2016 Calendar/Image in Month View Title
I am trying to show picture (based on condition) in Month View. I've managed to manipulate HTML Markup Functionality and now I am able to show picture in Calculated column but in "All Events&...
0
votes
2
answers
281
views
Calculated column issue in Sharepoint List - Excel Formula Given
I have tried to apply the following formula in the SharePoint list but somehow it gives an error. Can the same formula be used or any changes are to be made?
Please help me out. I have given the ...
0
votes
1
answer
309
views
How can I include the the original Item ID# in a new Item created/cloned using Power Automate flow?
I am using Power Automate to clone original Items in an existing SharePoint List. All is working perfectly, as the clone flow results in an exact copy of the original List Item. Now my users want to ...
1
vote
1
answer
346
views
Calculated Column IF OR AND 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:
= IF(
OR(
...
- 11
1
vote
0
answers
85
views
Calculated column dates are a day out when queried in Flow?
I've got a SharePoint list that is used to work out renewal dates of software. The three key columns are "NCE SKU", "NCE Purchase Date" and "NCE Renewal Date". The &...
- 57
0
votes
1
answer
18
views
Trying to use WEEKDAY function to make a column always return a Friday date, based off another date column
So far I have this, anyone know why it isn't working??
Goal is to return the first Friday after the [Est. Stock Date].
=IF(WEEKDAY("[Est. Stock Date]"=1,[Est. Stock Date]+5,IF(WEEKDAY("[...
0
votes
1
answer
61
views
Calculated Column formula to find two values in column
I have a Single Line Text Column(Content). I would like to create a calculated column that returns "Yes" if the Content column contains either of the two words- "Urgent" or"...
- 11
0
votes
1
answer
101
views
If statement comparing a calculated result to a number returns false even if they're the same
Number field "QuantiteStockageConteneur" = 7922.55
Currency field "TauxStockageConteneur" = 0.28
Currency field "StockageDeConteneur_ParPiedCube" = 2218.31
I have tried:
...
1
vote
0
answers
84
views
My Column Formatting No Longer Works (and the problem seems to only be on my computer...)
I have a column that is a Risk Index that calculates based on other columns. The equation calculates as following:
-50
0
30
70
100
-50 = Pending Review / 0 = Completed, no risk / 30 = Risk Mitigated ...
- 61
0
votes
1
answer
620
views
Calculated Column using current year (Today)
I need to create a calculated column that would need to show the years since a document was published. Notice that I'm not saying [Created]. The issue is that I have a document library where our ...
- 508
1
vote
1
answer
64
views
How can I use the doc ID in a calculated field
The document ID is this: EY3P5SDA5APP-512271551-6
and I would like to isolate the last digit (here 6) using a formula. I tried this formula:
=RIGHT([Valeur d’ID de document],LEN([Valeur d’ID de ...
0
votes
1
answer
849
views
How to Automate Percentage Complete on Sharepoint List when answers are text
I have a sharepoint list I am trying to figure out how to utilize power automate and flows to calculate a percentage score column that runs an initial calculation and then updates as new selections ...
- 3
0
votes
1
answer
60
views
SharePoint Calculated Field Formula List Generation
I have a SharePoint list content type made in legacy SharePoint. There are 143 calculated fields in this list. Is there a way to extract a complete list of all the calculations in all the columns at ...
0
votes
0
answers
43
views
SPOL Calculated Column Formula Help - Working Days
I’m trying to find the solution to 2 calculated column requirements. I have searched quite extensively and have only found partial, or partially working answers.
The two requirements are:
Work out the ...
- 1
0
votes
1
answer
231
views
List column that contains the sum of a property of a lookup column in the same list
I have a list of users that contains a lookup column for licenses. Each user can have multiple licenses so this column allows multiple selections.
The licenses list has a name and cost column. I would ...
- 103
0
votes
1
answer
26
views
Sharepoint Online Calculated Column Differences
I have 2 columns, 1 that has a calculated date depending on another column. Call that Date 1.
Second Column is another Calculated column. it looks at Date 1 and checks if its overdue.
The issue is ...
