Questions tagged [calculated-column-formula]

Question related to the Calculated Column

Filter by
Sorted by
Tagged with
1
vote
2answers
37 views

Calculate Column Based On Self (Circular Reference Workaround)

I have a date and time data type calculated column named "Date of Review" with the following formula: =IF([Status]<>"Select",NOW(),"") Formula explained, if the ...
0
votes
0answers
10 views

Calculated (calculation based on other columns) IF OR ISBLANK AND Sharepoint Lists error formula

Can anyone help? I can't work out why this is erroring. this section =IF(OR(ISBLANK[Tech £]),"", at the start in bold makes it error. =IF(OR(ISBLANK([Tech £])),"",IF(AND([Tech Pay ...
1
vote
1answer
26 views

My Calculated Field with a nested IF(OR) statement is returning error

I am trying to input an email address into a calculated field with the following formula (I have replaced proprietary information with generic information in this formula): =IF(OR([Customer Name]=&...
1
vote
1answer
24 views

Calculated Column 21 business days from start date

I have a field that is called Transmit Date, I need to generate a calculated column 21 business days from the date of this field. I have seen some other posts that fall within a week. I am curious how ...
0
votes
1answer
20 views

Remove characters from Text within a list - SharePoint 2016 onprem

I am trying to remove certain characters or only display certain characters within a list. The source field is called [Issue Status] and created a calculated column with the following: =LEFT([Issue ...
3
votes
1answer
33 views

Help combining SharePoint formula statements - DATEIF and ISERROR

I am running a SharePoint formula that calculates the length of time (in yrs, months, and days) before a project expires. In the "Will Expire" column the following formula is running: =...
1
vote
2answers
28 views

Hierarchy of Formulas In Calculated Columns

Is there an established hierarchy for formulas in calculated columns? Long story short our IT team got lazy and didn't complete user profiles. In order to get a proper name from a user field I'm using ...
2
votes
1answer
24 views

Create a view that shows entries only if they are 120 days from a date column

I have a list that tracks users loss date from the organization. This date is assigned from they day the arrive and are entered into the list (loss date). I am trying to create a view that would ...
1
vote
2answers
27 views

How to Create Calculate column using other Lists

I have a DB on my SharePoint site using several Lists. For one of the lists I need to count Total, Pending and Completed records from the other tables. There's one list for each department and on it ...
0
votes
1answer
67 views

Sharepoint list: How can I calculate difference between two dates without Fridays and Saturdays

I need a formula that can calculate difference between 2 dates without calculating Fridays and Saturdays. I need to use this formula to create a calculated column in list. Thank you
0
votes
2answers
20 views

I need it to show Yes automatically when the status is completed

I need it to show YES automatically when the status is completed and remaining as NO if is still in progress. Please share a code to make this happen. See the images attached. I have also tried ...
0
votes
1answer
23 views

Calculated Column Modification

I've got the following file name convention for images in an SP document library - CQYr UnqID Qual Name 2020 RR0000 Bus Driver Roger Rabbit I am using the following in a calculate column in ...
0
votes
2answers
34 views

Create calculated column that increases daily and resets on value of another column

Let's say I have two columns in a SharePoint list: [Invoice Age] - text (20, 23, 25) [Payment Status] - text (Not Paid, Paid) I'd like to create a calculated column [Invoice Age] that will increase by ...
0
votes
1answer
24 views

get count of duplicate values in a calculated column in sharepoint list

I have a sharepoint list with data as below. ID EN DE FR Val 1 EN1 DE1 FR1 1 1 EN1 DE1 FR1 1 1 EN2 DE2 FR2 2 1 EN2 DE2 FR2 2 1 EN2 DE2 FR2 2 1 EN3 DE3 FR3 3 ...
0
votes
2answers
26 views

Calculated Colum - Ordering nested IF statements

I don't know if what I am trying to do is possible, but this is the theory. I have a 3 columns in SharePoint Project State - TEXT Days Overrun - NUMBER Actual Finish Date - DATE The "Project ...
0
votes
1answer
22 views

How to use Calculated column to check if time is between 2 times

If I have an end time of "12.30 PM", how do I use calculated column to check if this is between lunch time from 12pm-1pm. I want to display "Yes" if it falls between and "No&...
0
votes
1answer
23 views

I am trying to create a IF/AND/OR statement in SharePoint, but it's not capturing what I need it to

The formula I have now is: =IF(AND([Add or Remove]="Add",[RealPage Complete]="No",OR([Add or Remove]="Terminate",[RealPage Complete]="No",[CMP Contact Unmapped]=...
0
votes
2answers
38 views

calculated field in SPO

I am using SharePoint online. My final goal is to show a calculated value. So what I am trying is: SUM all values in one column if the ID of the item is the same. It is a list of documentations and ...
1
vote
1answer
13 views

Help with nested If statements that format a phone number based on country

I am trying to create a calculated column with nested if statements. Due to my users not wanting to format the phone number correctly I am creating this new column to take the phone number in the ...
1
vote
3answers
38 views

Calculated text column in SharePoint Online results in a ‘0’

I would like either entry in the Status column to return a blank text value. Right now "Assigned" is resulting in a "0". =IF(Status="Discontinued","",(IF(Status=...
1
vote
1answer
18 views

Number formatting

I have a two choice columns that display same three choices: 0, 3, 9 Column 1 has a value of 9 Column 2 has a value of 3 I want to add 9 and 3 on a new column. Something so simple is giving me issues? ...
1
vote
1answer
31 views

Sharepoint list columns

Is it possible to create a SharePoint column based on choice (definition) that when selected populates the rating on another column? I will need to add sum of all scores: Thanks a million!
0
votes
1answer
26 views

Calcluated Column multiple IF with SEARCH statement coming up as #Name?

I want to search each cell in a column for a certain pharse. I figure I should use IF statements but since the following text can be anything I need to incorporate a SEARCH statement as well. Like at ...
0
votes
1answer
37 views

Calculating business days between dates and allowing a negative number

I have some working code that calculates business days between two dates: =(DATEDIF([Date Submitted],[Start Date],"D"))-INT(DATEDIF([Date Submitted],[Start Date],"D")/7)*2-IF(...
0
votes
1answer
19 views

SharePoint Calculated Yes/No Column

I've tried to create a calculated column in a SharePoint list that looks for a word in the title of the list entry and then returns yes or no based on if that word is included in the title or not. =IF(...
0
votes
2answers
27 views

Calculated Filed

I have created a calculated filed to convert a date field to another format, the issue I have is the calculated filed populate data for empty fields as well. Is there anyway it can only populate data ...
0
votes
2answers
25 views

Calculated Column Question Blank Dates

I have been stumped on this particular task for weeks. Any help would be greatly appreciated. I have three columns I am working with: Date Admitted to Hospital (date column), Date Discharged (date ...
1
vote
1answer
46 views

Mulitple IF Statements based on other criteria

I have this and it works on its own: =IF(ISBLANK([Basic Finish]),"Past Due",IF([FCM 4 Expected Date]>[Basic Finish],"On Time",TEXT([FCM 4 Expected Date]-[Basic Finish],"h:m:...
1
vote
3answers
27 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
22 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
23 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
34 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
20 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
41 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
40 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
19 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
32 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 ...
0
votes
3answers
55 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
51 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&...
1
vote
2answers
22 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 ...
0
votes
1answer
37 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
20 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
33 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
15 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
23 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
43 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
22 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 ...
1
vote
1answer
30 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
31 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 ...
0
votes
0answers
20 views

Difference calculation in SharePoint list - Calculated column with condition

I am working on a leave approval request that calculates company offs too. As per my logic, the first comp off should get subtracted from 12 which is the total number of comp off for this year going ...

1
2 3 4 5
12