Questions tagged [calculated-column-formula]

Question related to the Calculated Column

Filter by
Sorted by
Tagged with
0
votes
2answers
21 views

SharePoint online When date column has date in it or is TRUE update different column with preexisting selection

I'm having trouble figuring out what to do in this specific circumstance. I have a PowerApps form. In this form I have several fields but there are 2 that I'm trying to build a dependency on. A date ...
2
votes
2answers
106 views

Percentage formula showing extra numbers

I'm trying to get a SharePoint formula to show a percentage of time remaining on a contract. I'm nearly there but it's showing a lot of numbers to the right of the decimal point. I've tried a few ...
0
votes
3answers
43 views

How to count number of days from SharePoint list?

How to count number of days between two dates from SharePoint list using CAML query/JavaScript? Thanks
2
votes
1answer
24 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,...
1
vote
1answer
22 views

Yes or No in calculated fields instead of 1 or 0

Say, I have a SharePoint list with 2 columns, A and B. A contains a list with three items: one, two and three. B is calculated yes/no field, where I use the formula =If(A="two";TRUE;FALSE). This ...
1
vote
1answer
19 views

Add Sharepoint calculated field using AddfieldasXML - key cannot be null

I am trying to add a calculated field using the AddFieldAsXML method. And below is how I constructed the field xml. > fieldXML ="<Field ID=\"{ABB544B1-5433-4B49-A629-7B242D3548E5}\" > ...
0
votes
1answer
17 views

Working out the percentage of a term left from dats into months

I've got a field called Term months which contains the length of a contract in months and a field called Days Left which has the remain length of the contract in days. What would the formula be for ...
1
vote
3answers
21 views

If a column is NO, change another column to Closed

I have a SharePoint list with a status column which has the following in which works great. I'm looking to add to the formula dependent on another column - is this possible? If consent required ...
0
votes
1answer
21 views

Reference to custom column in formula raises “The formula refers to a column that does not exist” (SPO365)

I wanted to illustrate Calculated column in a List of a SharePoint 365 site. My List, "Inventory", has columns "Product", "Origin" (text), "Stock (Kg)" and "Security Level" (numbers) all mandatory, ...
2
votes
2answers
29 views

DATEDIF Calculation losing a minute

I have a timesheet for users to enter Start Time, End Time and Breaks. I've created this calculation to total the number of minutes worked. However, sometimes it loses a minute. How do I fix this ? ...
1
vote
1answer
19 views

Calculated Column: Nested if statement

Good Afternoon, Goal: Calculated column called "Certificate Status" (Data type returned: single-line of text) that identifies if an individual has provided the certificate for a required training. ...
0
votes
1answer
22 views

Populate Column with IMAGE based on another column's content

Here's a variation I'm having an issue with: I'm trying to populate a column based on the content of another column (choice field). While I'm currently doing this with a workflow, I know it can be ...
2
votes
1answer
26 views

Display text in a column based on what is in two columns

I have two columns [Impact] and [Urgency] that are choices - I would like to display text in another column based on the choices in those columns. if[impact]=High and [Urgency]=High then display "...
4
votes
2answers
50 views

Calculated column to show status if another has a value

I have a SharePoint list with a completed date column. I wish to have a calculated column called status so when the completed date column has a date in it, it shows "Closed" and when it is blank it ...
0
votes
2answers
7 views

is AVERAGEIFS possible in Sharepoint calculated column list?

Sorry I'm new to SharePoint. I am building a tool to track projects and would just like to ask if it is possible to have a column to automatically average all projects with the same 'priority name' ...
1
vote
1answer
37 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
0answers
19 views

Calculated column won't be created!

I am trying to create a calculated column. I tried below formulas: =([ContractFinalKm]-[Present/RealFinalKm])/ABS([Present/RealFinalKm]) AND =([Present/RealFinalKm]*100)/[ContractFinalKm] Neither ...
0
votes
1answer
28 views

Need help with displaying text depending on choice

I am trying to get this formula to work. I want: IF column ärende tilldelat is blank I want the calculated column to say "Ej tilldelad". If you have chosen "Tillkoppling" in the column Ärendetyp I ...
0
votes
2answers
16 views

calculated coluum formula error

Escalation status to be calculated as per below: Conditions: 1)if the status is "ETA Slipped" and Original ETA is > 15 days and Revised ETA is null Escalation status should be "ETA slipped beyond 15 ...
0
votes
1answer
16 views

Does Sharepoint allow IFS formula

I tested this on excel but can not translated to Sharepoint. I am trying to test for example if Column C has date that is greater than the one in column D = Submitted on time, if column C's date is ...
1
vote
1answer
20 views

SharePoint If Statement Long [closed]

I need a long statement for my calculated column to work. Basically I need it to say =IF[DeptID=201000 - Instruction],"Bob") OR =IF[DeptID=201000 - Instruction],"Tod") OR =IF[DeptID=201000 - ...
0
votes
1answer
28 views

Calculate number of days from date to today [duplicate]

I have tried and looked for the answer to calculate the number of days from a date to today's date and getting an error. Calculated columns cannot contain volatile functions like Today and Me. ...
0
votes
2answers
31 views

Display a date field in a specific format as single line of text using calculated column

I Need the Right Formula for the following Problem: I got a column that contains only date but sometimes it contains no Information. =DAY([AB])&"."&MONTH([AB])&"."&YEAR([AB]) This ...
0
votes
2answers
49 views

Problems on RIGHT function on Sharepoint

I have a calculated field which is filled in automatically thanks to a simple formula: =(X*X*Z/162-Y*Y*Z/162)*X This generate a number like: 1.637.037 I have a problem on adding RIGHT function to ...
0
votes
1answer
16 views

Simple ISERROR Calculated Field Assistance

I have a simple calculated field =[Total Reviews Failed]/[Total Reviews Completed]. The problem is that if these numerical columns are blank, The field returns #DIV/0!. Can I replace the #DIV/0! with ...
0
votes
2answers
24 views

Response date fucntion

I want to obtain the response date (number of days) based on other parameters. Used to use an excel file for document management, trying to upgrade to sharepoint and use meta-data. The formula i'm ...
0
votes
3answers
57 views

Column to display day of week

I am using the following formula to calculate the day of week based off of the Start Date column: =TEXT(WEEKDAY([Start Date]),"dddd") The problem is that when I create the event on the calendar view ...
0
votes
1answer
91 views

SharePoint Online: Calculated field with week number starting Saturday

I've found a few ways online to calculate the week number but none that allow me to start the week on Saturday. Here is the formula that I tested, but doesn't give me Saturday-Friday. =INT(([Time In]-...
1
vote
1answer
39 views

SPO Calculate Total Cost Per Group View

I will try to summarize my main goal if it's possible to achieve it. I have a list which contains the following columns: Invoice ID Number, Item Name, Quantity, Item Cost,Total Cost. What i want to ...
1
vote
1answer
50 views

Columns doesnt count right

So I got this example: The green value should show 01:00:00 but it doesnt. I think the formula I got "ROUND" it somehow (I got the code online). And I need this code so the value can exceed 24 hours. ...
0
votes
1answer
19 views

Calculated Column with Multiple Queries

This may seem really basic but I'm struggling to get my head around it. I have 2 boolean columns (Checked by TL and Checked by CSS), I need a calculated column to give me one of three results; 1: ...
0
votes
0answers
16 views

column formula error preventing subsite creation

When attempting to add subsite in Classic view SharePoint online, I receive - "Sorry, something went wrong. The formula refers to a column that does not exist. Check the formula for spelling mistakes ...
0
votes
1answer
8 views

adding years to existing date to produce expiry date ( SP list)

I have a list of training course that all have different levels of validy. So course A lasts for 3 years Course B lasts for 2 years Course C lasts for 1 year At the moment I have a column with ...
3
votes
2answers
154 views

If exceed 24 hours show “23:59:59”

So I got this calculated column called "Due time" with this format in it: =IF([Hours]<24&&[Minutes]<60;CONCATENATE([Hours]&":";[Minutes]);"23:59:59") I only get syntax error with ...
1
vote
1answer
21 views

Field Level Validation - Require Non-blanks on Numerical Fields

I have a custom SP list with a handful of non-required numerical fields. I'd like to write a column validation rule for each that would force users to enter any value >=0. Our users will be using Data ...
0
votes
1answer
28 views

IF Formula question

I am having some difficulty creating a formula in my Sharepoint List. I have a column that is a list of countries and I am trying to create a formula that will check the value of the column and spit ...
0
votes
1answer
18 views

Calculation Overload from Excel to Sharepoint

How can I convert this Excel Formula to SharePoint? =IF(AND(F6="REG",G6>=4,G6<8),0.5,IF(AND(F6="REG",$G6>=8,G6<=16),1,IF(AND(F6="HDY",G6>=4,G6<8),1,IF(AND(F6="HDY",G6>=8,G6<16)...
0
votes
1answer
37 views

Compare times in calculated column

I want to compare the time from a column called "Date and time" (datetime format) with a fixed time value. I have created a calculated column for this. =IF(AND(TIME([Date and time])>TIMEVALUE("06:...
0
votes
1answer
127 views

Need help to extend value to seconds

Got a calculated column with this formula: =IF(INT(Minuter/60)<10;"0"&INT(Minuter/60);INT(Minuter/60))&":"&IF(ROUND((((Minuter/60)-INT(Minuter/60))*60);0)<10;"0"&ROUND((((...
0
votes
0answers
17 views

Calculated Column in SharePoint 2007 list stopped working

I have a calculated column called 'Month' which adds in the month and year when each item was last modified. This has suddenly stopped working across all SharePoint 2007 lists in our environment and ...
0
votes
1answer
30 views

how to Insert multiple items into calculated column from single line of text

My single line text value is Colombia|d12fd403-83ac-4982-bc2c-3efdeb86e1f1; EP|1ac55076-7d65-4846-af18-67658fdf276a;Bronswerk|a5e873d7-61b6-454c-98cd-8c30ec17604f. I want to get the output in ...
0
votes
1answer
54 views

How to make a calculated column blank if other columns doesnt have any values?

This is my format in the calculated formula: =TEXT([Column2]-[Column1];"hh:mm:ss") I have value "2018-12-13 10:10:23" in Column1 but when Column2 is blank the calculated column shows "10:10:23" ...
0
votes
1answer
65 views

Calculated Column - Sharepont

I used calculated column with "IF" & "AND" Functions, If the logic is true or fall certain comments will be appeared to users// Comments "Verification Success", "Verification Failed". When I try ...
0
votes
1answer
74 views

SharePoint Online List Calculated Field HTML Tags is not working

in SharePoint migration process we found a list having two calculcated fields. 1. Color: using if condition based on field values, field will get/Set the color. 2. Display: will display the Title ...
2
votes
2answers
66 views

SPO Calculate the difference between two dates

I am trying to create a calculated column which shows the number of days' difference between two columns which have dates in them. I understand that I can get this with the following formula: =...
1
vote
1answer
28 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....
0
votes
2answers
310 views

Apply the friendly date format to a calculated column in a SharePoint list

I have a calculated column [Due date] with a simple formula, =[Event date]+13, and it's set to The data type returned from this formula is: Date and Time. However, it doesn't have the Display Format:...
0
votes
1answer
17 views

Calculated Columns in Sharepoint

I created two calculated columns to calculate the difference between Payment Date and Transfer Date, one of which is the "Duration" (between the payment and transaction) and the other is "Days Since ...
0
votes
2answers
35 views

If and else if in sharepoint list calculated column giving syntax error

I have two date fields date1, date2 and stores in column name result . I need the difference of the two dates to be calculated and store no of days in the result. Problem here is with two cases: ...
0
votes
1answer
27 views

Formula IF time between ranges (Calculated column)

I have a column "SignIn" that shows the date/time of a staff sign-in. I have another column "Late Flag" that will show if they are late or not. There are sometimes multiple sign-ins/outs throughout ...

1
2 3 4 5
11