Questions tagged [calculated-column-formula]

Question related to the Calculated Column

Filter by
Sorted by
Tagged with
3
votes
2answers
160 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
25 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
32 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
82 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
132 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
1answer
49 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
67 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
300 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
96 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
55 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
958 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
19 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
66 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
41 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 ...
0
votes
1answer
31 views

Increment count

I have an SPO list where I want to do an event tracker. It will track if an instance happens it increments by one, and we keep tracking for a year. At the end of the year the tracker reset to 0, and ...
0
votes
2answers
375 views

Calculated Column Field Date

I need to calculate date based on other column I'm using =Date(Year([endDate]),1,1) in a calculated field which needs to return data as DateTime, but it is giving me error: The formula contains a ...
0
votes
1answer
37 views

Calculated Field Syntax Error - Matrix Output

I'm quite new to this, could anyone point out why my calculated field isn't working Im trying to use a matrix to output a value of critical, high, medium or low The field Im using is ImpUrg_Total, ...
2
votes
1answer
649 views

Calculated Column display in filter of list

Hi I'm using the this approach https://sharepoint.stackexchange.com/a/118213/53772 to color an field of an list e.g. =IF([Status]="red","<div style='color:red;'>red</div>","error") ...
1
vote
1answer
18 views

Calculated column with multiple options depending on start date of event compared to today

I'm attempting to create a column ("30, 60, 90, +") that reports if an entry is within 30, 60, or 90 days out. One field is "Start Date" and I want the field to report '30' if the start date is ...
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
1answer
471 views

Update Yes/No field if Date is today

I am trying to create an automated SLA field I have a "Date Requested" Field and an "Outside of SLA" field. Date Requested is formatted as a Date, Outside of SLA is formatted as a Yes/NO / True/...
0
votes
2answers
13 views

Can't get conditional syntax correct

Have [Tier] column with choice values of 0,1,2,3. If [Tier] = 0 calculated column should = 0 If [Tier] = 1 calculated column should = 4 If [Tier] = 2 calculated column should = 24 If [Tier] = 3 ...
1
vote
2answers
75 views

If A completed move to B, and if B completed move to C

The scenario I have written supposed to work like below... A has 4 multiple choice options, (Completed, Suspended, Blocked, Cancelled) B has 4 multiple choice options (Completed, Suspended, Blocked,...
0
votes
1answer
126 views

Calculating “Task Aging” without weekends

I am calculating the number of days a task is aging. This works great: =DATEVALUE(TEXT(NOW(),"mm/dd/yyyy")) - DATEVALUE(TEXT([Created],"mm/dd/yyyy")) However, I need the counting to STOP when the ...
0
votes
1answer
33 views

Calculate max(num) +1 with calculated value? (Help please)

I want to do this: MANUEL 1 PEPE 1 MANUEL 2 LUIS 1 I want to get the number by a calculated value, but if the name is duplicated get the max(num)+1. Example: if (NEW.INPUT) exist find max(...
0
votes
2answers
29 views

String Calculation SharePoint 2010

I have the below string stored in a SharePoint Column: 11608,613,12,12643,12644,12656 I want to extract the value after the last comma. That is I need to extract only 12656 and use it for other ...
0
votes
1answer
98 views

Value of the Yes/No Column

What is the values of the Yes/No Column in SharePoint? 1, 0 Yes, No "Yes", "No" True, False Trying to use calculations: =IF([Col1]=Yes, 1, 0) So if it's true, put 1, else 2. But not matter what, ...
0
votes
2answers
779 views

Lookup Column in sharepoint with free text (No dropdown)

I have a free text of employee number in task form (Linked to form), when employee number is entered, I want to auto populate name and employee department. I can have source data in excel or separate ...
1
vote
3answers
171 views

SharePoint 2013 calculated column -compare dates

I thought this is easy but somehow I didn't get this right. I entered the following formula in the calculated column but the outcome is incorrect. For example, when [Target_Finish]=10/10/2019, the ...
0
votes
2answers
23 views

Calculated Column with IF ISBLANK, else if “ABC”, then return “XYX”

If [OH:Subsample ID] is blank, OR if it contains "ABC", then the calculated field should return result "XYZ". No idea how to put that into syntax. I can get two separate lines, but can't get them ...
0
votes
1answer
30 views

Calculated Column Not Displaying Anything

I have this really odd issue that works in 1 document library (first image) and doesn't for another (second image). I have 2 columns, column 1 called Published Date (Flow) that is of type string (...
0
votes
1answer
70 views

How can I populate a Library or List column name with the name of a file that has been uploaded?

Using SharePoint Online, how can I populate a column with the name of a file that has been uploaded? Seems all the related Q&A I am finding have more complex twists, this I hope is straight ...
0
votes
2answers
3k views

SharePoint Column Formula Validation

I need to check if a column (type Number) is an Integer (no decimal). I added this formula to the list validation: =mod([MyField],1)=0 But I get this error: The formula contains a syntax error or ...
1
vote
1answer
31 views

How do I insert “Created by” into a concatenate formula?

To have month view in a SharePoint calendar which displays the value of more than one field. I would like to combine the Title with the user's name using calculated column type. But Createdby can't ...
1
vote
1answer
144 views

Extract different text values from the title using calculated column

I am looking to find a way to extract text from the TITLE of an item when that text value could be different. Example: TITLE = N10 2019-2020 Kilo Project R Sec All Data Calculated column called ...
0
votes
1answer
386 views

Exclude weekends

I am working on a vacation calendar project. I have a custom lists with 2 columns, one being StartDate and the other NumberOfDaysTaken. I am using a simple SharePoint formula (=FromDate + [How many ...
0
votes
1answer
40 views

How to calculate the average of a field using a date range

I need to calculate the average of a number field called "Total" over a date range. I have a To and From date picker. I'm sure this can be done in Sharepoint, but I have no idea of the logic to use. ...
0
votes
1answer
202 views

SharePoint 2013 - Calculated Columns - Created timestamp is 6 hours into the future

I am trying to calculate the difference between created date and now() in calculated column in SharePoint 2013 but i have issue with the time zones. The servers are in Europe (UTC +2) and some of the ...
1
vote
0answers
66 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. ...
2
votes
1answer
421 views

Help with Traffic Light SharePoint Calculated Formula

I am wanting to create a column called "Traffic Light" on an existing SharePoint Library. Column Name = Traffic Light Data Type = Single line of text Formula: ="<DIV style='font-weight:bold; ...
1
vote
1answer
44 views

If Statement Giving Wrong Output Issue Calculated Column

I have this simple if statement where there are two columns. Column 1 = Score Column 2 = Test This is the If Statement Code: =IF([Score]>70,"A","F") If the score is greater than 70 it should ...
1
vote
1answer
1k views

Calculated Column - Choice Field

I'm just going to try and see if this is possible. I have a Choice Column that has three scheduled work shifts for employees to choose from. My supervisor wants the time selections to schedule that ...
1
vote
3answers
175 views

I want to display a blank field in the calculated column if both the reference fields are blank

I want to display a blank field in the calculated column if both the reference fields are blank, but SharePoint displays the default date as 30-Dec-1899. Formula I used in Calculated column: =IF(...
1
vote
1answer
48 views

Replace More Than One Comma Formula

I am trying to replace more than one comma from a single line of text field with an underscore, or some other symbol, but I want to replace it if a certain criteria is met - if the value in the field ...
1
vote
2answers
47 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 ...
0
votes
1answer
19 views

Giving one of two options

I am really new to all of this, but I'm trying to edit a column with the following: =OR(LEFT([Ticket Number],6) ="TASK00",LEN([Ticket Number])=0) where the user is required to enter their ticket ...
0
votes
1answer
64 views

Calculated column to determin a due date of a record

I'm looking for some help. I'm trying to setup a calculated field to determine a due date of a record. The due date needs to exclude weekends, and also is dependent on two addition columns. ...
0
votes
1answer
75 views

SharePoint Calculated Column, extract only ONE numerical value

I've seen the other articles that answer very similar questions but those formulas do not work for me, I needed to post a new question. I have the following column values: Leadership;#4# Character;#...
1
vote
2answers
479 views

incremental number at text string for use in calculated column

I need to create a unique ID number of each new project in the follow format 00-0000-000000 where the middle four digits are a number that increments by one for each new project. I have a list called ...

1 2 3
4
5
12