Questions tagged [calculated-column-formula]

Question related to the Calculated Column

Filter by
Sorted by
Tagged with
4
votes
1answer
3k views

How to filter tasks for previous calendar month

How to setup view, that would show all "Tasks" for previous month ? I can find first and last day of previous month. End of previous month: End_of_previous =DATE(YEAR([Activity Date]),MONTH([...
0
votes
1answer
13 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
21 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
12 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 ...
0
votes
1answer
920 views

How to Set Specific Date and time in Field of SharePoint List

I have a requirement where we need to create a view in a sharepoint to filter items created after 4pm on Friday until next week 4pm on friday. This is what i have accoimplished so far. Created a ...
1
vote
2answers
18 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
33 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
1answer
1k views

Copy to Clipboard Button in a Calculated Column (SharePoint Online)

I have a Text field called urlText in a Document Library List. Everytime a document is created, a workflow populates this field with the URL of the Document. I want to make a calculated column/button ...
0
votes
2answers
1k views

SharePoint 2013 calculated column multiple if statement error

can you please help me evaluate what is wrong with the if statement below, i need to use it in a SharePoint 2013 calculated column, but I keep getting an error. =if([Today]-[Last Reviewed Month/...
0
votes
3answers
2k views

Referencing Choice Field in Calculated Columns

I have a question regarding Calculated Columns. I have a Column Called "NextMileStone", this column can contain "PM010", "PM020, "PM040", "PM070", or some other choices that would be the "else" ...
0
votes
2answers
18 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 ...
4
votes
2answers
17k views

if and else if in calculated field

I have a column in SP called Pursuit status that contains various states a proposal can be in. For a diffrent project I need to lump them into 3 buckets (win - loss - pending) The fields below are: ...
1
vote
1answer
23 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
796 views

Sharepoint 2010 Calculated Column: How to get value before item gets modified?

I have a list that has a Status-field. If the status changes, a modified date in another column should indicate, when the status has been changed. My idea was to create a duplicate of this Status-...
0
votes
1answer
25 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
14 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
vote
1answer
39 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
2answers
895 views

SP2013 : Overriding Thumbnails column in PublishingImages library

I'm really stuck with this one, so if someone has a little time to help me on this... What i want : I have a pictures library in a site. It is even a PublishingImages library. a lot of my images are ...
1
vote
2answers
17 views

Multiple IFS in Sharepoiint Calculated columns

I am trying to write a formula in a calculated column that would populate shift hours base on shift name. The Shift title column is names "ERSShift". The different shifts available are "...
0
votes
0answers
14 views

Column Default Value Based on Root URL

I have a tab in an MS Teams team where users can view our upcoming corporate training sessions (i.e. modern Events). In each event description, I am putting a link to a sign up form. The sign up form ...
1
vote
1answer
25 views

Calculated column in SharePoint list - Conditional formula for finding difference

I am working on creating a leave tracker list. For that, I have added a column for Leave Type with 4 choices with 1 choice being company off. I want to add another column that says => Comp offs ...
4
votes
3answers
10k views

Calculated Column Formula how to replace Substitute

I need to grab everything after the last "-" (strings can have up to four "-") and if there is a space after the last "-" to not take the space. Examples: TEXT -(THIS ...
2
votes
3answers
7k views

how to get sum of a column in sharepoint 2013 list

Not sure if i would get any answer from here. I have a column number and title text. The data would look like this: Title Paid ---------- Jacob 200 Patrick 100 Jacob 100 Patrick 300 What i would ...
1
vote
1answer
115 views

Calculated Column condition formula

I have 9 dropdown columns on a form and am trying to create a calculated column KPI to show red if ANY of the drop downs are set as "tasked" or "pending". i cant seem to create a proper formula. i ...
1
vote
1answer
5k views

Which Excel functions are not supported by SharePoint calculated columns?

This MSDN article lists various functions that can be used in SharePoint calculated columns, and also includes the following disclaimer: Note Microsoft SharePoint Foundation formulas for ...
0
votes
1answer
650 views

Insert a break point in a column

Okay so I have a sharepoint list, and in this list is a Choice column where you can suggest multiple choices. I am exporting those choices to a calculated column. I want there to be a line break ...
2
votes
3answers
3k views

Javascript Functions in Calculated column

I am trying to use javascript functions within an calculated column which is working pretty fine e.g. if I use the following formula: ="<img src='/_layouts/images/blank.gif' onload=""{" &" ...
2
votes
1answer
8k views

Date column with default value as calculated value that gives the next Wednesday

I have a List Column that is Date only. I would like it to default to the next Wednesday (or today if today is Wednesday). What can I put in the Calculated Value field so that it defaults to ...
2
votes
2answers
7k views

Calculated column - using multiple AND and IF functions

I have been trying to figure this out and I cannot solve it. I am trying to create something which say IF column a = "High" AND Column b ="High" THEN "Red" should be inputted. So far I have... =IF( ...
0
votes
1answer
107 views

Translating a excel formula to a sharepoint calculated column formula

I have two excel formulas that are driving me crazy trying to get them to work in SharePoint 2010. Can anyone help with these? FIRST: =IF(AE206>=Q206,IF(AND(Q206>0,AE206>0,AG206>0),...
1
vote
1answer
127 views

Calculated Column - with datefield

I have one date column and one calculated Column. If date column is blank i want to save null in calculated column and if it is provided then calculated column should save that date. I tried =IF(...
4
votes
1answer
1k views

Add a line break in calculated field with formatted date?

I am trying to create a line break within a calculated column that displays the Date in DD MM YY format. The purpose is to utilize the field in a script which is altering the list view. My ...
0
votes
3answers
123 views

How to make a KPI appear next to a value in a column

I am trying to work my way around, adding KPIs next to a desired value. I have tried this so far by using this in the calculated column formula field <img src=" "/_Layouts/images/kpidefault-" &...
41
votes
7answers
110k views

How to use Today and Me in Calculated column

I want to use a combination of Today and Me in SharePoint 2013. I am trying it in Office 365 it gives me the below error Calculated columns cannot contain volatile functions like Today and Me. Can ...
1
vote
2answers
104 views

Another SharePoint query regarding calculation

Sorry I just need abit of help understanding this: I have column W, X, Y, Z. W is Dates X is Rota Y is Yes or No Z needs to be something, for example: If X is Rota1 and Y is Yes, then Z will be 1....
1
vote
2answers
61 views

Calculating Project End Date

In the project I'm working on I have created a column called Project Delivery Start Date and a column called Delivery Days. I want to use the start date and delivery days to create the Project ...
3
votes
1answer
374 views

Need help with calculating a date that only falls on a weekday

I have a column named "Date received" and I need to calculate a "Date Due". The due date is 3 calendar days after received however if that falls on a weekend it needs to be the next business day. I'...
0
votes
2answers
998 views

Sorting Calculated Numeric Columns Based on String Column

I am having a hard time getting a SharePoint 2013 list to sort in the correct order. We enter strings like this example 50-00-0. Then we want to split this into three columns so we may order them ...
0
votes
2answers
60 views

Calculating for Blank Fields and returning a date based on two other columns

I'm trying to correct the formula that decides if a ExDate is blank leave [YRexpCal blank, else find the FY of the ExDate and return FY as yy. My formula is: =IF(([ExDate])="","",IF(MONTH([ExDate])&...
0
votes
1answer
262 views

prioritization in SharePoint

I am new to SharePoint. I am trying to figure out how i can add ranking column as displayed below. I have a list with multiple columns, based on values of Count of priority qualifiers and submission ...
1
vote
3answers
273 views

How to create formulas in share point - quality assessment

I'm new to this forum and just want some help on the basics. I want to create a Quality Assessment form and want to know how to create formulas that are not Yes or No answer. For example: Score ...
9
votes
1answer
12k views

Calculated column increase date column by one day

I want to create acalculated column that takes a date field and sets the value to the date, increased by one day. How can this be done? For ex: If date field value: 20/10/2015 calculated result will ...
-1
votes
3answers
893 views

Calculated column with nested IFs (more than 7)

I have calculated column for custom IDs with code: =IF([ID-Type]="Hema", IF([External-ID-Hema]>999,CONCATENATE("18",[External-ID-Hema]), IF([External-ID-Hema]>99,CONCATENATE("180",[External-ID-...
1
vote
2answers
26 views

Adding days on calculated column from monday to friday

I need to add a calculated column in SharePoint where I have to add +1 day from Monday to Thursday and +3 on Friday but I don't know how to do it. I did some tests with WEEKDAY without success.
0
votes
1answer
32 views

Validation setting for Date Sharepoint

I want to apply validation setting so that my "ActionDate" should be always greater than 2 year and 6 month. I can able to set greater then today but how to set it should greater then 2 year ...
1
vote
1answer
21 views

check tow fields values in sharepoint validation settings

I have a form to create a task and the user can edit it after manager approve it, I don't want to allow the user to submit after approval if field [Task Status]="Waiting on someone else" or ...
2
votes
2answers
53 views

Calculated Column: Prevent date from changing - SharePoint 2013 online

Need some help please - currently working on SharePoint 2013 online. I have a choice column (drop down) called 'Stage'. In 'Stage', I have 3 choices; Start Pause End I also have 3 calculated columns ...
0
votes
1answer
25 views

Validate Due Date less than 12 months

I have a field named "Due Date" and I am trying to add validation so users cannot enter a date more than 12 months from today (current date). This is SP2010. I've tried this formula in the ...
0
votes
0answers
18 views

Start Time (EventDate) when used in calculated field, showing minus 1 day if AllDay is selected

I am working on a SharePoint 2010 Calendar. Creating a column with a calculated field type. Using =Text([Start Date]), "ddd, dd, mmm") and for some reason, When I am selecting the Event as ...
1
vote
1answer
16 views

Sharepoint 2013 List validation - two fields contain text

Pardon this newbie question on SharePoint list validation, I have been researching this for days SharePoint 2013 SharePoint LIST validation since you cannot compare two fields in FIELD validation Two ...

1
2 3 4 5
11