Questions tagged [calculated-column-formula]

Question related to the Calculated Column

Filter by
Sorted by
Tagged with
0
votes
0answers
6 views

Column Default Value Based on Root URL

I have a tab in an MS Teams team where users can view our upcoming corporate training sessions (i.e. modern Events). In each event description, I am putting a link to a sign up form. The sign up form ...
1
vote
1answer
16 views

Calculated column in SharePoint list - Conditional formula for finding difference

I am working on creating a leave tracker list. For that, I have added a column for Leave Type with 4 choices with 1 choice being company off. I want to add another column that says => Comp offs ...
4
votes
3answers
10k views

Calculated Column Formula how to replace Substitute

I need to grab everything after the last "-" (strings can have up to four "-") and if there is a space after the last "-" to not take the space. Examples: TEXT -(THIS ...
2
votes
3answers
7k views

how to get sum of a column in sharepoint 2013 list

Not sure if i would get any answer from here. I have a column number and title text. The data would look like this: Title Paid ---------- Jacob 200 Patrick 100 Jacob 100 Patrick 300 What i would ...
1
vote
1answer
114 views

Calculated Column condition formula

I have 9 dropdown columns on a form and am trying to create a calculated column KPI to show red if ANY of the drop downs are set as "tasked" or "pending". i cant seem to create a proper formula. i ...
1
vote
1answer
5k views

Which Excel functions are not supported by SharePoint calculated columns?

This MSDN article lists various functions that can be used in SharePoint calculated columns, and also includes the following disclaimer: Note Microsoft SharePoint Foundation formulas for ...
0
votes
1answer
641 views

Insert a break point in a column

Okay so I have a sharepoint list, and in this list is a Choice column where you can suggest multiple choices. I am exporting those choices to a calculated column. I want there to be a line break ...
2
votes
3answers
3k views

Javascript Functions in Calculated column

I am trying to use javascript functions within an calculated column which is working pretty fine e.g. if I use the following formula: ="<img src='/_layouts/images/blank.gif' onload=""{" &" ...
2
votes
1answer
8k views

Date column with default value as calculated value that gives the next Wednesday

I have a List Column that is Date only. I would like it to default to the next Wednesday (or today if today is Wednesday). What can I put in the Calculated Value field so that it defaults to ...
2
votes
2answers
7k views

Calculated column - using multiple AND and IF functions

I have been trying to figure this out and I cannot solve it. I am trying to create something which say IF column a = "High" AND Column b ="High" THEN "Red" should be inputted. So far I have... =IF( ...
0
votes
1answer
106 views

Translating a excel formula to a sharepoint calculated column formula

I have two excel formulas that are driving me crazy trying to get them to work in SharePoint 2010. Can anyone help with these? FIRST: =IF(AE206>=Q206,IF(AND(Q206>0,AE206>0,AG206>0),...
1
vote
1answer
126 views

Calculated Column - with datefield

I have one date column and one calculated Column. If date column is blank i want to save null in calculated column and if it is provided then calculated column should save that date. I tried =IF(...
4
votes
1answer
1k views

Add a line break in calculated field with formatted date?

I am trying to create a line break within a calculated column that displays the Date in DD MM YY format. The purpose is to utilize the field in a script which is altering the list view. My ...
0
votes
3answers
123 views

How to make a KPI appear next to a value in a column

I am trying to work my way around, adding KPIs next to a desired value. I have tried this so far by using this in the calculated column formula field <img src=" "/_Layouts/images/kpidefault-" &...
41
votes
7answers
108k views

How to use Today and Me in Calculated column

I want to use a combination of Today and Me in SharePoint 2013. I am trying it in Office 365 it gives me the below error Calculated columns cannot contain volatile functions like Today and Me. Can ...
1
vote
2answers
104 views

Another SharePoint query regarding calculation

Sorry I just need abit of help understanding this: I have column W, X, Y, Z. W is Dates X is Rota Y is Yes or No Z needs to be something, for example: If X is Rota1 and Y is Yes, then Z will be 1....
1
vote
2answers
60 views

Calculating Project End Date

In the project I'm working on I have created a column called Project Delivery Start Date and a column called Delivery Days. I want to use the start date and delivery days to create the Project ...
3
votes
1answer
370 views

Need help with calculating a date that only falls on a weekday

I have a column named "Date received" and I need to calculate a "Date Due". The due date is 3 calendar days after received however if that falls on a weekend it needs to be the next business day. I'...
0
votes
2answers
974 views

Sorting Calculated Numeric Columns Based on String Column

I am having a hard time getting a SharePoint 2013 list to sort in the correct order. We enter strings like this example 50-00-0. Then we want to split this into three columns so we may order them ...
0
votes
2answers
60 views

Calculating for Blank Fields and returning a date based on two other columns

I'm trying to correct the formula that decides if a ExDate is blank leave [YRexpCal blank, else find the FY of the ExDate and return FY as yy. My formula is: =IF(([ExDate])="","",IF(MONTH([ExDate])&...
0
votes
1answer
260 views

prioritization in SharePoint

I am new to SharePoint. I am trying to figure out how i can add ranking column as displayed below. I have a list with multiple columns, based on values of Count of priority qualifiers and submission ...
1
vote
3answers
271 views

How to create formulas in share point - quality assessment

I'm new to this forum and just want some help on the basics. I want to create a Quality Assessment form and want to know how to create formulas that are not Yes or No answer. For example: Score ...
9
votes
1answer
12k views

Calculated column increase date column by one day

I want to create acalculated column that takes a date field and sets the value to the date, increased by one day. How can this be done? For ex: If date field value: 20/10/2015 calculated result will ...
-1
votes
3answers
876 views

Calculated column with nested IFs (more than 7)

I have calculated column for custom IDs with code: =IF([ID-Type]="Hema", IF([External-ID-Hema]>999,CONCATENATE("18",[External-ID-Hema]), IF([External-ID-Hema]>99,CONCATENATE("180",[External-ID-...
1
vote
2answers
24 views

Adding days on calculated column from monday to friday

I need to add a calculated column in SharePoint where I have to add +1 day from Monday to Thursday and +3 on Friday but I don't know how to do it. I did some tests with WEEKDAY without success.
0
votes
3answers
2k views

Referencing Choice Field in Calculated Columns

I have a question regarding Calculated Columns. I have a Column Called "NextMileStone", this column can contain "PM010", "PM020, "PM040", "PM070", or some other choices that would be the "else" ...
0
votes
1answer
25 views

Validation setting for Date Sharepoint

I want to apply validation setting so that my "ActionDate" should be always greater than 2 year and 6 month. I can able to set greater then today but how to set it should greater then 2 year ...
1
vote
1answer
18 views

check tow fields values in sharepoint validation settings

I have a form to create a task and the user can edit it after manager approve it, I don't want to allow the user to submit after approval if field [Task Status]="Waiting on someone else" or ...
2
votes
2answers
50 views

Calculated Column: Prevent date from changing - SharePoint 2013 online

Need some help please - currently working on SharePoint 2013 online. I have a choice column (drop down) called 'Stage'. In 'Stage', I have 3 choices; Start Pause End I also have 3 calculated columns ...
0
votes
1answer
22 views

Validate Due Date less than 12 months

I have a field named "Due Date" and I am trying to add validation so users cannot enter a date more than 12 months from today (current date). This is SP2010. I've tried this formula in the ...
0
votes
0answers
16 views

Start Time (EventDate) when used in calculated field, showing minus 1 day if AllDay is selected

I am working on a SharePoint 2010 Calendar. Creating a column with a calculated field type. Using =Text([Start Date]), "ddd, dd, mmm") and for some reason, When I am selecting the Event as ...
1
vote
1answer
15 views

Sharepoint 2013 List validation - two fields contain text

Pardon this newbie question on SharePoint list validation, I have been researching this for days SharePoint 2013 SharePoint LIST validation since you cannot compare two fields in FIELD validation Two ...
0
votes
1answer
24 views

Add a calculated column with if statements

New in this forum. And I have a quick question: I have a choice column with this values: Kg Lb Ton And I would like to add a column that multiplies if I choose Kg or Lb or Ton and convert ...
2
votes
1answer
884 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 ...
0
votes
1answer
17 views

SharePoint time difference in minutes - wrong result

I created a simple SharePoint list and I am having problem with a calculated field. I have a "Duration" field that calculates the minutes between the start and end date/time columns, using the formula:...
1
vote
1answer
22 views

Issues with calculated field syntax

Our school district uses O365 Sharepoint in the cloud. I've been tasked with creating a calendar in Sharepoint for all users for conference rooms reservations. I have NO experience with Sharepoint. ...
1
vote
1answer
54 views

Date Difference in minutes SP Calculated Column

I have a SharePoint Calculated column that is supposed to calculate the difference in hours and minutes between two dates. This is the expression I am using for that: =TEXT([IN/OUT Time]-[Staging ...
0
votes
1answer
21 views

Sharepoint list with calculated column - formula IF does not work

Working on a SharePoint list with a calculated column with a date, which I want to make dependent on priority (column is called Prioriteit), and date of creation (Datum melding). I made this formula ...
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
22 views

Validate a column only if another column is not blank - Excel Formula not working

I need to add a column validation to my list. The requirement for the validation is that the column [Body] cant be empty if the column [Status] is one of this statuses: "1-Cancelled" or "2-Awaiting ...
2
votes
1answer
843 views

SharePoint 2010 using REPLACE and FIND functions multiple times in a calculated field

I'm working on a SharePoint 2010 site where I have a field which contains three letter characters which are separated by a hyphen e.g. AMO-SMF. I have created a calculation which can FIND and ...
0
votes
1answer
27 views

SharePoint Calculated Column by searching another column for various strings

I am working with a SharePoint 2016 list that contains one column with names and other values. I want to use a calculated column to search that "name" column and return another value if that "name" ...
0
votes
1answer
22 views

How to calculate the difference between two data in the same list column

I want to know how to calculate the difference between two data in the same column of a list. It is useful if we are monitoring the changes (daily-monthly-etc). TX for your suggestions on how to make ...
0
votes
1answer
2k views

Calculating overdue status in a Sharepoint list using calculated column

Hoping someone can help as I am relatively new to Sharepoint. I've created a list to track completion of internal audit activity. It includes a status column and due date column. I'm trying to ...
6
votes
2answers
678 views

Calculated Column Based on Version Not Updating

I'm using a calculated column [DocumentStatus] and versioning to try and do some colour-formatting based on Approval Status. I have Major & Minor versioning turned on. Since I cannot use Approval ...
1
vote
2answers
35 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
1answer
16 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 ...
0
votes
1answer
24 views

Sharepoint list for vacation accruals and usage

I've been trying to create a sharepoint list that displays the current available balance of an employee's vacation hours. I'm having issues creating a column that is calculated based on several other ...
0
votes
1answer
14 views

Date must be more than 2 Years from today

I have a date column in a SharePoint list. This column need to be filled with dates that 2 years forward from today or more. How can I write the validation formula for this? Much appreciated if ...
0
votes
1answer
23 views

Share point calculated columns with nested if statements

can anyone please help me in cracking the logic in below if statement, that i am trying to solve. Below is the logic i am trying three columns Item status, Treasury status and MD status if Treasury ...

1
2 3 4 5
11