Questions tagged [calculated-column-formula]

Question related to the Calculated Column

175 questions with no upvoted or accepted answers
Filter by
Sorted by
Tagged with
6
votes
0answers
100 views

Formula in spreadsheet being converted to a custom list

I am currently trying to take an existing spreadsheet and replicate this as a SharePoint list. The spreadsheet contains the following columns: Received - This will be a number column with 0 decimal ...
4
votes
1answer
3k views

How to filter tasks for previous calendar month

How to setup view, that would show all "Tasks" for previous month ? I can find first and last day of previous month. End of previous month: End_of_previous =DATE(YEAR([Activity Date]),MONTH([...
3
votes
3answers
389 views

Is there a way to calculate the number days between two list items in a list?

I have an InfoPath library/list in SPO. I need to calculate the number of days between two different list items. It's to show the number of days since the last work event. Is there an easy way to ...
3
votes
1answer
71 views

Yes No calculated field for closed/created in last 7 days and items open or in-progress

I would like to create a calculated field that outputs "Yes" or "No" if an item in my SharePoint list has been created in the last 7 days, closed in the last 7 days (field in status column), or 3) ...
3
votes
0answers
102 views

SharePoint Conditional Validation / Formula

In SharePoint Online can i perform the following? I have a column to calculate the date difference between Last Visit Date and Next Visit Date. Last Visit Date is already provided, and users will ...
3
votes
0answers
12k views

Count number of rows in another list

I have two lists, A and B. I wish to find out the number of rows in list B that have a certain value in list A (through a lookup column) and display this in list A. Is this possible at all using a ...
3
votes
0answers
7k views

Calculated column formula for not equal

Hi can I use the <> operator in a SharePoint Calculated column? The following formula is accepted but I'm not sure it is supported. My requirement is: If any values equal High AND are not ...
3
votes
0answers
613 views

Sharepoint 2010 Nested If Limit

In SharePoint 2007 the limit for the number of nested IF statements was 7. On the 8th SharePoint would return and error. On my 2010 site I made an error and added 8 nested IF statements and there was ...
2
votes
1answer
27 views

Showing the time remaining between two Date columns as a perentage

I've got two columns 'Contract Commencement Date' and 'Contract End Date' and I'm trying to show the time remaining as a percentage in a third column. Using Flow I've got a field to show todays date,...
2
votes
2answers
289 views

Calculated column displaying “#Name?”

I'm trying to display KPIs that show how long a list item spend in each status. Column name = Time In 5. =IF(OR(ISBLANK([Date entered 6. ]),ISBLANK([Date Entered 5. ])),"No KPI dates",DATEDIF([Date ...
2
votes
1answer
74 views

SharePoint Absolute Reference - Fixed Value in Calculated Field

I'm looking for a way to get the absolute reference of a specific "cells" in a SharePoint calculated column ? For example I got this SharePoint list: A1 - B1 - C1 A2 - B2 - C2 My calculated field ...
2
votes
0answers
75 views

Subtracting work days not working

I tried a formula for subtracting 3 working day, I found googling and a piece of it does not work for me. I found this: =IF(WEEKDAY(DATE(YEAR(RStartDate),MONTH(RStartDate),DAY(RStartDate)-3),2)=6,DATE(...
2
votes
2answers
127 views

Replace second “?” with Calculated Column

The string below contains two "?" what calculated column formula should I use to replace the second "?" with "&" but not remove the "?" from other strings that have only one "?" Could anyone ...
2
votes
2answers
77 views

Calculate time including IF column value

I have the basic time value formula, but I am having two problems; The first is with this Total Comp Time formula: =TEXT([Start Time]-[End Time],"h:mm") It does display the difference in time as ...
2
votes
1answer
912 views

How to use a lookup column's additional field in a workflow?

Background I have two lists. One is a list of voltages and their corresponding costs. This is to be used as a lookup for other lists. Voltage Cost 120 2.50 240 ...
2
votes
2answers
107 views

Show image based on calculated column value

Why does SharePoint not understand to change the image when I use If statements? Specifically when I use it on a calculated culumn. Building a status (project/risk) page and I use Probabiliy and ...
1
vote
2answers
21 views

How do I create a calculated column that displays Yes or No if ANY other columns in the row have the value “Yes”?

I have a series of 3 Choice columns that are populated by a MS Form to be either "Yes" or "No". I am wanting Column4 to show Yes if ANY of the columns 1 2 or 3 contain the value &...
1
vote
1answer
12 views

How do I write validation statement to require a day of the week?

I have a field in a custom list called "WeekOf". I have a default statement that populates the date as the following Monday, but some people are entering for future weeks and then add a ...
1
vote
2answers
18 views

Calculated column formula not working when referring to a Yes/No type column?

I am working on a SharePoint list validation to automate some process. I have Column A which is a Yes/No type column and I have Column B which is a calculated column that will take input from column A....
1
vote
1answer
23 views

Calculated Date Difference Question

Before submitting this question, I've tried researching this in the forums and have attempted multiple variations of suggested formulas with no success. So apologies in advance for asking what seems ...
1
vote
1answer
39 views

Calculated Column - Days Difference - Syntax Error

I have a SharePoint List and I'd like to have a column to calculate the days it take to solve a ticket (Solution date - Submission Date). I tried to create a calculated column, by using a formula = ...
1
vote
2answers
17 views

Multiple IFS in Sharepoiint Calculated columns

I am trying to write a formula in a calculated column that would populate shift hours base on shift name. The Shift title column is names "ERSShift". The different shifts available are "...
1
vote
2answers
19 views

Using DATEIF only if another column returns 2 out of the 3 column entries

I'm attempting to calculate the difference in dates (the current date & Date Opened column) only if the Status? Column reads Open or Hold. I've tried: =IF(Status?,"Open" OR "Hold",(DATEDIF([Date ...
1
vote
1answer
21 views

SharePoint List - Time Calculation

We have a change control SP list and do aging metrics on how long an item is open. I have a calculated field to calculate how long an item has been open, but am getting a negative serial date as a ...
1
vote
2answers
44 views

Calculated column from a multiple lines column

I made this formula for a calculated column that works from with single line columns, but am unable to make it work with a multiple line column. =IF(ISERROR(FIND("flight",[shipping])),"ground","...
1
vote
2answers
46 views

Increase number of decimal places in SharePoint calculated column while list exceeds the list view threshold

I am facing an issue while I need to increase number of decimal places of list calculative column. I need it in SharePoint online - unfortunately the list contains more than 40K records (and ...
1
vote
1answer
9 views

Marking item as “Delivery not met” sharepoint list

New to SharePoint lists.. trying to find a way to mark items that did not meet the deadline. I have the column "Expected Completion Date" and "Date of Completion" (Both are in mm/dd/yyyy format). I ...
1
vote
1answer
21 views

Calculating Formula for days and text

Trying to write a formula that calculates the expected completion date based on the priority of the project day it was assigned. For two of the priorities, it needs to be a text response in the ...
1
vote
2answers
31 views

Calculated Column as Title on SharePoint Calenar

I am working in SharePoint to create a calendar that will pull the information from the "Content Category" column and the "Title" column into the title field. To do this, I am trying to create a ...
1
vote
1answer
41 views

Calculating Fiscal Month

I've seen a number of calculations for fiscal year and quarter, but am hoping someone can help me a calculated column for fiscal months. Our company's fiscal months run from the 29th through the 28th....
1
vote
2answers
36 views

=IF(DATE(YEAR([Compliance Date]),MONTH([Compliance Date])+11,DAY([Compliance Date])), “Due”, “Not Due”)

I am trying to create an If statement that looks at a date column in sharepoint and if its 11months after that date the column displays yes. any help on this as: =IF(DATE(YEAR([Compliance Date]),...
1
vote
0answers
52 views

Getting “#NAME” from SharePoint Survey calculated column

In the results for a Survey done in SharePoint 2013 (survey list - type that I'm "inheriting", if you know what I mean). I noticed we're getting "#NAME" in some of the areas we're pulling down. ...
1
vote
2answers
40 views

How to sort column fields and get its total

Dear SharePoint Experts, I would like to ask for tech support regarding on the screenshot that I attached. As you can see from the image, I have a My Requests, All Items, My Balance views, in My ...
1
vote
0answers
34 views

Need to count entries

I am looking to make it so that each item in a list has a column that shows how many total items there are in that list that share the same value in a column as that list item. If that makes sense. To ...
1
vote
1answer
97 views

Calculated column between two date fields but if one is blank use another date field

I would like a calculation that gives the number of days between 2 dates (start date & end date) but if end date is blank I would like it to use todays date. How can I set this formula up?
1
vote
2answers
701 views

How to Conditionally Require Data in more than one SharePoint Column

I am attempting to calculate the outcome a status field based on the outcome of other columns. They are all Test Fields. For Example: "Overall Approval Status" column is to be based on Accepted/...
1
vote
2answers
106 views

How do i get a choice column to change from YES to NO depending on another column using calculated columns

I'm trying to uses nested ifs to change what a choice column displays but I'm not sure if this can be done or if I'm doing it properly. What I'm trying to have accrue is once the next review date ...
1
vote
1answer
234 views

Perform a calculation based on other criteria

sorry in advance supernoob here failing hard and need help. I'm trying to create a timesheet in sharepoint that will eventually be a powerapp then go into PowerBI and I'm struggling bad with the ...
1
vote
0answers
224 views

Sharepoint Calendar View Filter - Show items in current year

I have events on my calendar that have Start Time dates in 2016, 2017, and 2018. I have a calculated formula on my Calendar list named Year Calculated Event - Current. The formula is =YEAR([Start ...
1
vote
1answer
446 views

Add/Subtract amounts from column in one list to “cell” in another list

I am trying to build two lists to keep track of our purchase orders we have. My two lists looks like this: List 1: PO +-----------+-----------+-------------+ | PO | Amount | Used/Left |...
1
vote
2answers
165 views

Is it possible tpo use the excel weeksum function in SharePoint to return the week number from a date column?

I am new to SharePoint and I know that in Excel you can use the WEEKSUM function to return the week number from a date column. Is it possible to do the same in SharePoint 2013? If so how can this be ...
1
vote
0answers
25 views

Multiple Content Types Duplicating Values in Calculated Column when updating CT

List Setup I have a list setup with 3 content types. Content Type A, Content Type B, Content Type C The list has 3 choice columns setup for each content type. This enables the choices available to ...
1
vote
0answers
38 views

Sharepoint 2013 Form validation - I have 4 date fields I want to validate on. The first 2 are compulsory second 2 can be blank or filled in

I am having dificulty getting my validation formular to work. Field Names: DateAdded (first date) Expectedleave (second date) these are compulsory and the Expectedleave must be equal to or greater ...
1
vote
0answers
160 views

Calculated Column formula not working correctly on refresh

I have a List in SharePoint 2010 which contains a list of Vendors. The List has three columns: Vendor, Inactive, and SortVendor. SortVendor is a calculated column with the formula: =IF(ISNULL(...
1
vote
0answers
51 views

Calculated Column based on multiple other columns that may not exist

Is it possible to create a Calculated Column based on other columns that may not exist? Here is the hypothetical scenario. We have a Term Set such as the following: * Animals * Mammals * ...
1
vote
0answers
76 views

Calculated column based on specific elements from another site column

I have this site column in a Sharepoint 2013 list showing a few deadlines. I need to create a calculated column next to the one in the picture, that gives as a result the number of days between one ...
1
vote
0answers
78 views

Submitting and emailing an InfoPath quiz

I need the Submit button to email the quiz results and also submit the results to SharePoint. I first used a rule button and created 2 rules, 1 to email and 1 to submit the form to SharePoint. It ...
1
vote
1answer
93 views

Formula syntax error

I'm trying to add more than one if's (also) statements inside a formula, but it is not working because of a syntax error. What I'm trying to do is if actual sign date column is less or equal to the ...
1
vote
2answers
63 views

Calculated column formula that always adds 1 to new item in List

What formula I can use in a column so the value in the field always increases by 1 every time a new item is created?
1
vote
1answer
101 views

Calculated Value Changing when issue tracking list has been resolved

*Hope someone can help me with this issue. I am currently using the following formula to calculate a number of hours a ticket has taken to complete. =IF(Completed="","",INT(((Completed-Created)*...