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,279
questions
0
votes
1
answer
65
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'...
0
votes
1
answer
102
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 ...
- 309
0
votes
1
answer
317
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 ...
3
votes
2
answers
34
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
2
answers
2k
views
Sharepoint-Online Caculated End Date shows as blank on Gantt View
I have noticed a number of issues with DataSheet/Gantt View when using calculated columns, i will list others at the bottom, however my main concern here is my gantt chart not showing an end date for ...
- 1
0
votes
0
answers
18
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
18
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 ...
- 2,213
1
vote
1
answer
665
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(...
- 2,297
1
vote
1
answer
204
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 ...
1
vote
1
answer
645
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
55
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,...
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=...
1
vote
1
answer
23
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 ...
1
vote
1
answer
49
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 ...
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 ...
6
votes
2
answers
26k
views
Calculated column issue with if statement and yes/no field
I have seen other similar questions about calculated fields/columns and Yes/No fields, but only regarding syntax. I don't have an issue with the syntax (I think) but I can't get an IF statement to ...
- 103
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 ...
2
votes
2
answers
442
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
...
1
vote
1
answer
11
views
Can't return two differnt text values with forumula
I'm using this code:
=IF(Age>=5,"1W","0", =IF(Age>=10,"2W","0"))
I cant get it to return the values of 1W or 2W in the field. Any help would be ...
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"...
0
votes
0
answers
20
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
76
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;&...
0
votes
1
answer
24
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 ...
2
votes
1
answer
936
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; ...
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
1
vote
1
answer
387
views
Extract description rather than URL in calculated column to use it with another url
Let's say the first column is UID having number 12345. You will be directed to the URL after you click on 12345.
The second column I am trying to create is like a URL to some website followed by 12345....
0
votes
1
answer
1k
views
Calculate Columns to create Leaderboard
I have created an InfoPath form for a company-wide Step Competition that will have different communities competing against each other for walking the most amount of cumulative steps. Each form ...
CommunityBot
- 1
0
votes
2
answers
166
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 ...
0
votes
2
answers
248
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")
1
vote
2
answers
40
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 ...
1
vote
2
answers
33
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
3
answers
2k
views
Calculate the week from a date, Jan 1st = week 1
I'm looking for a calculated column formula which will calculate 1st January 2022 as week 1. I'm in Europe, so, like most of the world, my date field 01/01/2022 = 1st January 2022.
I would like 1st ...
1
vote
1
answer
32
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 ...
1
vote
1
answer
1k
views
Calculated Column based on [Start Time] showing series start date instead of event start date on recurring event
On my Calendar list I have recurring events. I have a view that wants to filter on the [Start Time] of the event but that field isn't available in the filter options of a view. After some research I ...
CommunityBot
- 1
1
vote
2
answers
62
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 < ...
0
votes
1
answer
40
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 ...
- 286
1
vote
2
answers
43
views
ISBLANK WITH OTHER CRITERIA
I have a sharepoint calculated field that looks at a date and values in two other fields to populate with either a blank or a date.
First criteria:
Units > 0
Second criteria:
Type = "RDS"
...
- 416
2
votes
1
answer
235
views
SharePoint list Formula to calculate hours NOT WORKING PROPERLY
I tried the below Formula to calculate the time between the start time and end time in hours but not working properly.
=IF(ISBLANK([Column2]),"0",TEXT([Column2]-[Column1],"h"))**
...
1
vote
1
answer
70
views
Calculated Date Column IF statement
I have a list in SharePoint on-premises site that has a calculated date column (named Acknowledge Due Date) with this formula:
=IF(WEEKDAY((Created))=1,(Created+3),IF(WEEKDAY((Created))=2,(Created+3),...
- 2,213
0
votes
1
answer
711
views
Filter over Calculated column over the SharePoint List view does not work
I have a SharePoint list. It has a column called 'Closed Date', it keeps only date value of date only type and optional. Now I have created one more calculated column called Closing Year. The formula ...
1
vote
1
answer
454
views
SharePoint list Formula to calculate hours
I have a SharePoint list with two columns Start Time and end time. I want the Formula to calculate the time between the start time and end time in hours only.
If the end time is still pending (empty) ...
1
vote
1
answer
368
views
Creating a calculated field to show 'Red' 'Amber' or 'Green' based on a due date column
I am trying to create a calculated column to return "Red" "Amber" or "Green" based on a due date.
I have a date column called "Due Date" and I am trying to add ...
1
vote
2
answers
575
views
Calculated Column - Days Difference - Syntax Error
I have a SharePoint List and I'd like to have a column to calculate the days it take to solve a ticket (Solution date - Submission Date).
I tried to create a calculated column, by using a formula = ...
1
vote
1
answer
16
views
Can I use a calculated column to show whether a vehicle check submission has already been completed on any given day
I am creating a list which will be used for daily forklift checks that need to be completed via a power app. What I would like is a column that can calculate whether there has already been a check ...
1
vote
1
answer
236
views
SharePoint list calculated column based on status in other columns
I have a list built with three levels of approval on an item. I want to add a calculated column to quickly show the status of the item based on those approvals.
So if all three approvals exist, the ...
0
votes
1
answer
226
views
Calculated Column- First Name from Full Name
I'm trying to write a calculated column formula to return the First Name from a Full Name column. The issue is, that the full names are displayed in different formats:
Jacob J. Smith
Peter Grabowski
...
- 1,261
1
vote
3
answers
2k
views
SharePoint List Column Conditional Calculation
In a new list item form,the user wants to have a column to display month & year(pre populated on opening the form) based on the below condition.
"1. If the current date is > 15 ,then that column ...
1
vote
1
answer
474
views
Calculated column with IF condition
I'm writing from Italy. I have a list of 5 columns: one of them is called "available" and it's a yes/no column.
Another column is a text column called "last name". I want to create ...
1
vote
2
answers
71
views
SharePoint List Column Calculation formular with conditional Calculation
I am working on a fuel issuance list in SharePoint online with 3 different vehicle engines.
Using the columns given below:
Requester Name: (Name Field)
Vehicle Location (Dropdown List)
Vehicle ...
0
votes
1
answer
326
views
How to check for a date/nulls and compare to the current date?
=IF(ISBLANK[Version-created];"";[Version-created]+365)
=IF([Version-created]="";"";[Version-created]+365)
I created these formulas to first detect if there is a date in ...