Questions tagged [calculated-column-formula]

Question related to the Calculated Column

Filter by
Sorted by
Tagged with
1
vote
3answers
33 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 ...
0
votes
1answer
47 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
vote
1answer
26 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 ...
0
votes
1answer
35 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"...
0
votes
1answer
33 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=...
0
votes
1answer
63 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
1answer
48 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
1answer
20 views

Fomular to automate project final status

Please i need help. I need a formula on share-point online list. I have 3 columns namely: Child care status, Education status, Micro business status. Each of the columns above have (Completed, ...
0
votes
1answer
34 views

Column Validation with If Formula

Need your assistance in writing formula. I have SharePoint List with a choice field Named as "Status" with "Active" and "InActive" as choices. By default this is set to ...
1
vote
1answer
401 views

SP2013: Calculated column returns 0 instead of “” when list items change

I've got a simple list with 3 columns; Employee Name, Status, and a calculated field. Status column is a dropdown choice between "Active" & "Inactive" Calc field formula is: =IF(Status="Active",[...
0
votes
3answers
74 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 ...
1
vote
2answers
29 views

Sharepoint Online Modern = Right formula crashes

Using Sharepoint Online Modern experience. I am trying to use the right formula in a column with a calculated value and get an Sharepoint error. I have a column named LOCATION and has a string value ...
1
vote
2answers
113 views

Multiplying hours/minutes in a calculated column

I am trying to use calculated columns to sum the difference between two times then multiply that result by a number. The SharePoint list looks like this: The "Start Time" and "End Time&...
0
votes
1answer
39 views

Minus 5 hours 30 minutes from a date

I have a column of type as date and time. I want to minus 5 hours 30 minutes to produce a new time in a new column - Please advise?
0
votes
1answer
46 views

Calcualted column formual “=TEXT(NOW(),”mm/dd/yyyy“)-[Last Update]” will show 44,055 if the [Last Update] column is empty

I am working on an online SharePoint site, and I created a calculated column with the following formula:- =TEXT(NOW(),"mm/dd/yyyy")-[Last Update] now the formula is working well if the [...
1
vote
2answers
138 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
22 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
45 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....
0
votes
5answers
50 views

Sharepoint Calculated Compliance Column

I have an SharePoint list with columns: Title (Single Line Text column) DteofAwrd and CurrentDte (both date columns) ComplianceStatus (calculated column) I am using the below to determine compliance ...
0
votes
2answers
32 views

Sharepoint list have german date convert into Monthnumber

I have a German client sahrepoint list and I want to convert month from date column in number. Like Month and Year in German: Want to Extract Month and year in separate column for further calculation ...
4
votes
2answers
23k views

if and else if in calculated field

I have a column in SP called Pursuit status that contains various states a proposal can be in. For a diffrent project I need to lump them into 3 buckets (win - loss - pending) The fields below are: ...
1
vote
1answer
37 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 ...
0
votes
1answer
53 views

Nested IF Statement in SP List Calculated Column

I need to create a Nested If statement based on several columns. Column 1. CurrentDate (Date) Column 2. DteofCQ (Date) Column 3. CQTitle (Text) Column 4. ComplianceStatus (Calculated) There are 8 ...
1
vote
1answer
59 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
75 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
1answer
37 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
11k 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
122 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
6k 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
699 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
4k 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
9k 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
10k 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
182 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
128 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
2k 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
135 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-" &...
42
votes
7answers
118k 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
105 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
72 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
435 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
1k 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
61 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
297 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
305 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
15k 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
1k 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
66 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
1answer
36 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 2
3
4 5
13