Questions tagged [calculated-column]
Calculated columns are the SharePoint equivalent of Excel formulas. They allow you to display values that are calculated from the values of one or more other columns. These calculations can be made using standard mathematical operators and SharePoint's built-in functions.
1,261
questions
1
vote
1
answer
15
views
ISBLANK and DATEDIF with two date columns where there are sometimes blanks in both
I am trying to figure out the correct formula for my SharePoint calculated column. I have two date columns [Date in Queue] and [Date Completed]. I need to calculate number of days between the two ...
- 11
1
vote
1
answer
17
views
Question on formula
I have a calculated column in SharePoint at the moment, it uses a formula to pull in a month name, number of holidays in the that month and then multiplied by 3. The formula I am using is:
IF([Month]=“...
- 21
0
votes
0
answers
11
views
Help with Calculated Column Formula on SharePoint
I have a SharePoint List that I need to build a calculated column for but am having issues trying to put together the formula.
For the purpose of this forum, the columns needed in the formula are &...
- 1
1
vote
1
answer
13
views
How to create a calculated column that gives date excluding weekends?
I have a SharePoint list with "Request raised date" and "No. of days" by which the request should be completed.
Now, I want another Date column which adds 'No. of days' to the &...
1
vote
1
answer
23
views
Count option is unavailable in calculated column
I have a query regarding on SharePoint list. In the column heading, there is an option named "Totals" and under this "None or Count" drop-down is available. I have a "Status&...
- 27
1
vote
1
answer
13
views
SharePoint list validation formula issue
This is the first time asking here. I'm trying to write a formula for list validation but I cannot understand what I'm missing.
For this formula I need to chain 2 conditions
[Start date]<= [End ...
- 11
1
vote
1
answer
10
views
Cannot get past syntax error
My coworker has written this code but continues to get a syntax error. We've checked field names and all are correctly spelled.
Any ideas what else could be wrong?
=IF(ISBLANK([Start Date]),"&...
0
votes
0
answers
18
views
Formula Help Needed: Calculating Dates to Exclude Weekends
I am trying to set up several dates on a list in SharePoint. The dates all relate to each other, and I want to exclude weekends when calculating these dates.
I have three date columns: “Request Date” -...
0
votes
1
answer
28
views
Sharepoint. ID Column exists in list, and all items view but not in list settings
How do I find my ID column? It exists in my List and in my all items view but not in list settings.
I am trying to do a second field where I concat the ID to a string.
Where is it? how come I cant ...
- 99
1
vote
1
answer
20
views
SharePoint Calculated list IF statement based on two columns results
I have columns called Refresh Due and Refresh Priority and I want the results in these columns to provide a result in a third column called Priority Order.
Refresh Due is a calculated column that ...
0
votes
0
answers
34
views
SharePoint List Nested IF statatments
I'm trying to write a code that checks the status of a column and if it's blank it then checks a different column and so on until it returns data. This is my syntax so far but it comes back as invalid:...
- 1
1
vote
1
answer
59
views
SharePoint Site column of type Single line of text with specific validation expression
I'm looking for a validation formula to validate a site column in SharePoint site and make it only accept only lower case English characters, numbers, and only hyphen, I tried below formula but it's ...
- 217
0
votes
1
answer
165
views
SharePoint calculated column convert text to number?
Actually, I have searched about this topic. But I didn't get why it is useful to convert text to a number.
I'm new to SharePoint.Can anyone please help me with that? And give any example based on that ...
0
votes
2
answers
157
views
Is it possible to get email from person column in SharePoint calculated column?
Is it possible to get email from person column in SharePoint calculated column?
Please write it, if it is possible.
0
votes
2
answers
42
views
How to get first letter of second string in SharePoint calculated column?
I have a SharePoint list, where I got the first letter in a calculated column by using the LEFT function.
How can I get the first letter of a second string?
0
votes
1
answer
107
views
SharePoint list calculated column Concatenate Function
I have Created a Date and Time column, Based on on that i have taken calculated column to concatenate Text and Date. But it is not showing Date format in calculated column. Can you please help with ...
0
votes
2
answers
37
views
Help with formula for calculated column in SharePoint
I have a SharePoint choice column entitled month of year, I want to look at entries where the month selected is August, I want to then look at any holiday entries for the month of august which are ...
- 21
1
vote
1
answer
12
views
Calculated Column: Nested If
I am new to SharePoint and I'm trying to enable a calculated column in a SharePoint list with the following formula but it's not working quite well. It is not throwing me any Optimize nor Test ...
- 11
1
vote
2
answers
27
views
Can you please show me any example related to this AND Condition in SharePoint Calculated column
I have taken a SharePoint list based on budget. I wanted to take a Calculated column named Budget Scarcity based on Budget Amount and Used Amount. I wanted to Use AND Condition, but was getting errors ...
0
votes
1
answer
55
views
SharePoint calculated column with hyperlink
I have a SharePoint list as "Training Courses" and inside this, I have added a calculated column named "Website URL".
Here, My requirement is if a user enters the website name in ...
- 21
1
vote
1
answer
80
views
SharePoint calculated column replace all spaces
This is my requirement:
Here, I have taken a single line of text column with 2 spaces in the column field. I have used that column as a reference in a calculated column. By using a formula I can able ...
2
votes
1
answer
127
views
SharePoint calculated column if contains text
I have a SharePoint list (Issue Tracker), here I created a calculated column like Product Name and the formula is:
=IF((FIND("Laptop",Title)),"Laptop")
And, My requirement is when ...
- 21
0
votes
1
answer
24
views
Calculated column Update daily
I have a column that calculates the days until expiration for certifications. To do so I use the Today function:
=[Certification Expiration Date]-TODAY()
I have noticed that this type of column doesn'...
- 25
0
votes
1
answer
16
views
Add text to a column if calculated column contains a certain number
I have a SP online list with several number columns that are used for scoring. A numerical score is input into each column. I have an 'Overall' column (calculated column) that adds up the column ...
- 11
0
votes
1
answer
56
views
How to use Regex in a Calculated Column's formula for finding a string pattern in another column?
I'm trying to generate an ID on emails that come on my site. Essentially, when the email subject matches the regex, it generates an ID based on that pattern. My question is how do I use regex on SP's ...
- 115
3
votes
2
answers
29
views
Where am i going wrong with this calculated formula (ISBLANK)?
So on my calculated formula in share point, I can get it to do what I want it to do with the following formula:
=IF(DATEDIF([Start of Absence],NOW(),"d")<28,"No","Yes")...
0
votes
0
answers
12
views
Why is a "number" column with a calculated default value working for PDF and not for PPTX?
I have a column "Year" as number which is set to have a calculated default value:
=YEAR(Today)
It works when I upload PDF, but not PPTX.
What could cause the issue?
See all info here:
- 121
1
vote
1
answer
15
views
Nested if statement not working to create a Risk Log
I am trying to move risk logs to MS list and have encountered issues with the if nested statements. I have tried different variations of the formula below.
The formula seems to work with half of the ...
- 13
1
vote
1
answer
166
views
SharePoint Online List View- Filter by a date column and Start and End of Month dates
I would like to create a list view that filters out items with 'Date Joined' in the the current month/year.
I have created two calculated columns - StartOfMonth and EndOfMonth
StartOfMonth = DATE(YEAR(...
- 23
1
vote
1
answer
31
views
Date formula in calculated column - valid
I have a Microsoft list with two relevant columns 'ACTION Status' and 'Due Date'. I am looking for 'ACTION Status' to return Text values based on the 'Due Date' column values. I an using the following ...
- 11
1
vote
1
answer
147
views
Creating a Link to Filtered Document Library using JSON Column Formatting
I have a SharePoint Online list and a document library. Within the list, there is a Lesson Name column (Title is internal name). I'm trying to add a column (to the list) that includes a link to the ...
1
vote
1
answer
28
views
Formula for calculated value showing as metadata
I have a User who was able to automatically tag files with metadata based on folders in SharePoint per the video https://youtu.be/Tq5zibnXkhY.
However, one attribute is a calculated value, =TEXT(Today,...
- 11
1
vote
1
answer
21
views
I need to calculate a Date only when there are Term days
I need to Calculate a Term Deadline based on the expiration date BUT only when there are Term Days.
My current formula for "Term Deadline:
=[Expiration Date]-[Term Days]
The problem is that the ...
- 13
1
vote
1
answer
28
views
Calculated "Average" Column Shows Too Many Decimals
I've got column where I'm using a formula to calculate the average of a few other columns. It keeps showing a bunch of decimals even with the decimals set to 2 on the column.
I've seen where some ...
- 35
0
votes
0
answers
8
views
Calculated field for time not always working
I have a calculated column on a SharePoint list to calculate hour(s) and minute(s) for the time taken to do a training course.
It works for most of the entries, but some that are 1 hour, aren't ...
1
vote
1
answer
17
views
Is it possible to get the greatest common divisor of two numbers via calculated column?
What I'm attempting to do is create a calculated column that displays an image's aspect ratio based on the computed columns Picture Width and Picture Height.
This video (https://youtu.be/b-W64DHKO8o?t=...
- 11
1
vote
1
answer
11
views
Calculated field based upon Date and Time field serves up a weird number
I have a list of learning courses. One column is a Date and Time column showing when a learning course will run.
I wanted to use this column to build a unique course ID. However when I set up another ...
- 11
0
votes
2
answers
18
views
Forumla in sharepoint field not producing correct value
Hello and thank you for looking at this. I'm trying to use this:
=IF(Age<=5,"0",IF(Age>=6,"1w",IF(Age>=10,"2w",IF(Step="Sent and Uploaded","0"...
- 27
2
votes
2
answers
138
views
How to calculate overall score based on columns values?
I am seeking for help with SharePoint online list, where I have choice columns with following answer choices: 1,2,3,4,5 where each choice has own value e.g.
1 = 30 points
2 = 60 points
3 = 90 points
...
- 21
0
votes
0
answers
14
views
How to implement a SharePoint calculated column inside Canvas app form and have its value changed directly inside the Form
We have 5 SharePoint fields been displayed inside a Canvas app forum:-
Q1 of type currency
Q2 of type currency
Q3 of type currency
Q4 of type currency
Percentage of type number
and i want to ...
0
votes
1
answer
29
views
What's wrong with my Calculated Column Formula Syntax?
I have a SharePoint list with 2 columns: Age and Age Group. The age groupings I want to display in the second columns are:
Under 16
16-20
21-30
31+
This is the formula I've tried:
=IF([Age]<16;&...
- 764
0
votes
1
answer
20
views
Calculated Formula on Column not working
=IF([First Date]>0,0.15,IF([Second Date]>0,0.3,IF([Third Date]>0,0.45,IF([Fourth Date]>0,0.6,IF([Fifth Date]>0,0.75,IF([Sixth Date]>0,0.9,IF([Seventh Date]>0,1,0)))))))
This is ...
0
votes
0
answers
32
views
no entered date
Can I get a bit help how to combine if(not(isblank) and if(isblank) in formula below for SharePoint? I need to merge two formulas into one.
=IF(AND(NOT(ISBLANK(Date_end);(Date_Implemnted));""...
- 1
0
votes
2
answers
38
views
Combine 2 columns as 1
Hi I am trying to create a calculated column that lists 2 columns right next to each other as 1. The first column is "Date" and the second column is "Time Zone". I want them ...
- 11
0
votes
2
answers
97
views
Previous Month in Text
I am trying to show previous month in a calculated field but it just defaults to "Jan" regardless of the date. What am I doing wrong here?
=TEXT([Created]-1,"mmm")
- 103
1
vote
2
answers
37
views
Make a column display only when data is in it
I am trying to make a column display only when data is entered in it. If it has no data, then the column should not display on the page. I tried this formula, but it isn't working. I will appreciate ...
- 11
1
vote
2
answers
25
views
ElseIf options for calculated fields?
I'm trying to calculate a date column based on what data exists in two other date columns. Is that possible?
Basically, in 95% of cases, the Planned Date is always 5 days from the Submission Date. I ...
1
vote
1
answer
23
views
SharePoint Choice Fields
I have a list that has a multiple choice field (more than one choice can be selected) and I want to query that field to find out how many of the choices were selected.
So if the field has 5 choices ...
- 25
1
vote
2
answers
34
views
How to combine ISBLANK AND if
I am trying to pre-populate a column in SharePoint to show as 'live' where a separate column is either blank or has a date > than 'today' and to show as 'closed' where a date is entered and is < ...
- 11
0
votes
1
answer
28
views
Choose non Blank field
I have a sharepoint list with multiple calculated fields. I am looking to roll the fields into one by choosing a final status. There are 6 columns. I want the formula to look at all six columns and ...
- 1