Questions tagged [calculated-column-formula]
Question related to the Calculated Column
818
questions
0
votes
1
answer
17
views
Show as blank rather than 0.00% (Decimal showing as %)
How can I set the calculated formula on SharePoint list when the result is 0 (0.00%) to show as a blank and if there is a value do the following calculation =Figure/100?
Hope you can help me.
2
votes
1
answer
52
views
How to create a calculated column to pull the year of Date column 1, and if blank, pull year from Date column 2, if that's also blank then put Hold
I am trying to create a calculated column that looks at the Date1 column and pulls only the year into the new column. If the Date1 column is blank, then bring in the Date2 column. If Date2 column is ...
- 23
0
votes
0
answers
8
views
Difference between two Time Stamps in Minutes Excluding Out of Business Hours
I want to know if it's possible to calculate add minutes to a Created Time stamp in Only Business Hours (09:00-17:00).
Example:
16:00 - 17:30(current time) = 60 min
08:50 - 09:10(current time) = 10 ...
1
vote
1
answer
23
views
Making required condition in SharePoint list dependent on another field
I have a SharePoint list with several choice, date, text and yes/no fields. I want to make some fields conditional on another field containing a certain word.
So for example, I have a field called '...
- 23
0
votes
1
answer
16
views
Calculated Column next date
I am trying to add a calculated field to a list that takes another field (which allows multi values) and picks up the next date.
See Below - Meeting ID is a lookup from another list and is populated ...
- 1
1
vote
1
answer
18
views
Error in my IF statement
My IF statement is shown below and i am using Calculated Function on SharePoint
when the word Traffic Signals is used on [Please select which discipline you require access to?], the row (called ...
0
votes
1
answer
18
views
SharePoint Online Modern Form Conditional Formatting (Column Rules)
I am working on a SharePoint Online Modern Form solution using the OTB conditional formatting. There are three rules that I am trying to apply, here is the example.
Two Columns:
COL1 - Choice Field - ...
- 49
0
votes
1
answer
26
views
My Syntax Error Calculation in SharePoint List is not worked T_T
I have so so BIG problem!!
So, I'm trying to generate the verdict syntax calculation, but it shows the wrong answer.
Please help me T_T.
The pictures show the filled value from 2 columns before. In ...
- 1
1
vote
1
answer
33
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
20
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
1
vote
1
answer
31
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 &...
0
votes
1
answer
127
views
Set field as mandatory if the field is dependent on another field
Can I set a field as mandatory if the field is dependent on the status?
Below is the form to insert information for a new plan. How to set Purpose of Visit field as mandatory.
If I use the below ...
- 27
1
vote
1
answer
28
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
17
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
23
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]),"&...
1
vote
1
answer
56
views
How date can be dependent on status changing
I developed a travel plan list on SharePoint. In the plan, for travel, there are 3 drop-down stages:
Planned
Completed
Cancelled
But the issue is anyone can select the "Completed" status ...
- 27
0
votes
0
answers
20
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” -...
1
vote
2
answers
35
views
Microsoft LISTS Validation Settings question
In my list I have 3 columns, one titled 'Review Status', one titled 'Type of System' and one titled 'Needed Information'.
I am trying to write a validation that says
If the Review Status equals ...
0
votes
1
answer
47
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
24
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
35
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
63
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
0
answers
7
views
Help with Calculated Column Formula
This is probably very simple, but I can't seem to get it working. I need to build a calculated column formula based on the below in my SharePoint list.
Calculated Column Name is called Status
The ...
- 1
0
votes
1
answer
538
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
535
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
85
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?
1
vote
1
answer
21
views
Solution for Creating a Formula that Utilizes Two Separate Columns As Conditions For The Output of a Calculated Column
Hi SharePoint experts,
I have two columns in a Sharepoint List, one titled "Budgeted Amount" and the other "Contract Expiration Date".
What I'm trying to do is create a calculated ...
- 13
0
votes
1
answer
745
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
45
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
14
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 ...
1
vote
1
answer
198
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
387
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
28
views
SharePoint Validation: If Empty or Null
I need help with a validation formula in a SharePoint Text column where:
column can be empty ( the default state when list is opened)
column can be null (the state after a value is deleted)
must be ...
- 103
0
votes
1
answer
36
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
113
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
33
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")...
1
vote
1
answer
17
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
48
views
SharePoint List Validation Not Working
I have this list validation expression:
=IF([Enrolled], IF(ISBLANK([Email Address]), FALSE, TRUE), TRUE)
But its not triggering any validation. I also tried some simpler validation expressions:
=IF(...
- 11
1
vote
1
answer
70
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
35
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
22
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
34
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
317
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
1
answer
46
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
21
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 ...