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
1 vote
1 answer
8 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
13 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
8 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 ...
0 votes
1 answer
15 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
21 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?
1 vote
2 answers
27 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
29 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
19 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 ...
  • 13
0 votes
1 answer
32 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
38 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
18 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
28 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
22 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
56 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 ...
  • 107
0 votes
1 answer
33 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
18 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
22 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],&...
0 votes
0 answers
14 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 ...
1 vote
1 answer
32 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
16 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 ...
  • 31
1 vote
1 answer
34 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
30 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
13 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
40 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
0 answers
15 views

Calculate difference between dates and roundup

In SharePoint, I am trying to calculate days between two dates. My calculated formula is: =DateClosed-Created This works, but how can I change this to have the following result: If the outcome in ...
0 votes
1 answer
91 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
76 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
21 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 &...
0 votes
1 answer
16 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
17 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
41 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
46 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 ...
0 votes
1 answer
65 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
0 votes
0 answers
9 views

I'm trying to calculate the Hitrate percentage of a manager for each week of the month, there are months when we have 5 weeks as well

I'm trying to calculate the Hitrate percentage of a manager for each week of the month, there are months when we have 5 weeks as well. Below is the code I'm using: =IF(OR(HRW01+HRW02+HRW03+HRW04=4,...
  • 1
0 votes
0 answers
18 views

SharePoint 2016 - Created Start/End filter columns, but recurring events aren’t working. calculated fields only stamp the first & last event day/times

So I have set up four Calendar Views and four corresponding Overlays to display three colors based on filters: Scheduled Tasks: Category of “Scheduled Task” w/ Blue Overlay color Current Tasks: ...
  • 25
1 vote
1 answer
37 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
232 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 ...
0 votes
1 answer
45 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
26 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
67 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 ...
0 votes
1 answer
24 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 ...
0 votes
1 answer
40 views

Condition based on today's date in calcualted field

I have a calculated column inside a solution Table in Power Automate but I need the condition to be check with a value now() - the current date, not a hardcoded value like this. However when I try to ...
0 votes
0 answers
34 views

How do I create a calculated value for a column in SharePoint online

I have a list within SharePoint online. One column is for doc type (choice field), another column is expiration date (Date/time field) and third column is status column(Choice). When the document in ...
0 votes
0 answers
10 views

I enter large formula into calculated column and click ok, no errors appear, and when i click edit the column the majority of the formula is gone

I enter a fairly large formula consisting of nested IFs, ANDs, & OR statements and the formula is accepted without errors when I click ok. When I then hit edit to view the formula again, most of ...
  • 1
0 votes
1 answer
116 views

Remove Trailing Zeros from Sharepoint List Lookup Field that is based on a Calculated Field

I have two Sharepoint Lists, one of which has a Primary ID field that is a Calculated Field that simply equals the Sharepoint generated ID field. Table 2 has a Lookup field that pulls in this new ...
0 votes
0 answers
27 views

Creating calculated columns using data from calculated columns

I tried to create a calculated column which uses data from other calculated columns in the same list. I'm getting an error message, that the columns do not exist even though i can select them from the ...
  • 1
1 vote
1 answer
125 views

Calculate the week from a date, Jan 1st = week 1

I'm looking for a calculated column formula which will calculate 1st January 2022 as week 1. I'm in Europe, so, like most of the world, my date field 01/01/2022 = 1st January 2022. I would like 1st ...
  • 2,329
0 votes
1 answer
30 views

Can I make a calculated column that uses text in the boolean comparison but returns a number?

I'm trying to make a calculated column with a 5 level conditional as follows: =SI([Impacto Inherente]="Crítico";400;(SI([Impacto Inherente]="Mayor";300;(SI([Impacto Inherente]=&...
0 votes
1 answer
45 views

Calculated column, formula to count how much time left before deadline

I have 2013 list (on premises) and I have a column "Deadline date". I need to create a calculated formula which 30, 60 and 90 days before will show such text, 30, 60 or 90 days left before ...
0 votes
2 answers
76 views

Date and Time Calculation in Sharepoint Excluding Weekends and displayed in minutes

The code below is working well to calculate the time between two dates and excluding weekends. However, I would like to modify it to just return the number of minutes in number format so I can do some ...

1
2 3 4 5
24