Calculated columns are the SharePoint equivalent of Excel formulae. They allow you 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.
9
votes
2answers
1k views
How can I display a message on a list item when within X days of a date?
I'm using SharePoint 2010 and I'd like to create a column in Task List that will display a message (not generate an email: just display a field text) such as "URGENT" or "Almost Due" when the date is ...
6
votes
4answers
749 views
Check if more than 3 hours
I need a calculated column to display "Yes" if the current time is more than 3 hours after the value of another column. This is what I've come up with:
=TEXT(Today-[AddTime],"h")>3
But ...
6
votes
2answers
10k views
Using calculated field to retrieve substring of another field
Suppose I have a field that contain the following data:
==========================
| Email Address |
==========================
| Jack@wonderland.com |
| Peter@nobodyland.com |
| ...
5
votes
2answers
13k views
Calculated value for Title field?
I would like to create a calculated value for the mandatory Title field:
<username, created by> - DD-MM-YYYY
That is I want the title to be filled in with the user name (the user that creates ...
5
votes
4answers
2k views
calculated field - formula for ageing
Again a calculated field question, How can I create a calculated field to find out, what is the age of the entry.
In the list I will have a created date field, from that I would like to create a ...
5
votes
5answers
10k views
How to use [Created By] in a column?
SP 2007, calendar list. Want to add a column that gets its default value from "Created By" but then allows that to be edited. This is what I tried:
Create column
Give it a name
Leave type set to ...
4
votes
4answers
6k views
Calculated column with a Date
I am creating a list whereby I want following function:
Current Day or Today - 2 days = Output Date
i.e.
23/12/2010 - 2 days = 21/12/2010
Is it possible to change the current dates dynamically ...
4
votes
2answers
16k views
Column calculated value to get the current date time?
I have tried using =TEXT(Today,"mm-dd-yyyy hh:mm:ss") but that only returns the 5/5/55 00:00:00, it won't give the time. How do I get both the date and time with a calculated formula?
3
votes
3answers
2k views
Sharepoint - Calculated Column showing different colours based on list item age
Marc,
I understand you are a busy man, but if you can spare atleast sometime to my query below then I will really appreciate the gesture.
Background:
We get several requests (items on SharePoint ...
3
votes
1answer
2k views
Calculated field Date formula
I have a date field, and it has the date in the format - 11/2/2011 11:40 AM
How ever my requirement is to get the date in the format - 02 Nov 2011 11:40 AM
For that I created a calculated field for ...
3
votes
2answers
1k views
IF statement, Trouble with formula
I have created a simple list where we record time spent on troubleshooting customers. But now I would like to create a simple formula for a Calculated field, but I can't get it to work.
We have two ...
3
votes
2answers
3k views
How to find out the number of the week in a calculated field?
How can I calculate the number of the week based on a date in a list? I tried several examples I found on the internet, but none worked correctly?
The formula should comply with the ISO standard ...
3
votes
1answer
518 views
Calculated Hyperlink Column - Lost ID-Field Value Upon Editing
I created a calculated column with the following formula:
="<a href='../X_C03 Staff Members/DispForm.aspx?ID="&ID&"'>View</a>"
and included this into a view. The column works ...
3
votes
2answers
722 views
How to create a calculated column based on Workflow Status column
I want to create a calculated column based on Workflow Status column which the system is not allowing. The error is "One or more column references are not allowed, because the columns are defined as a ...
3
votes
5answers
8k views
How do you Sum a Calculated Column?
I have a calculated column in a list and I would like to get a sum of that column in the view. I can't use datasheet view, and I don't like the idea of a workflow because the user only sees a few ...
3
votes
2answers
431 views
SharePoint Calculated Field Limitations - Need Work Around
I have the following in a calculated field. I need to add many more IF statements but when I start adding more to the code I begin to get Syntax Error. I need a workaround to make this work.
...
3
votes
2answers
2k views
How to set the Default value of a Date/Time column to next Friday?
I did some poking around in the documentation for Calculated field formulas, but I don't see anything that would help me do this. Does anyone know if it can be done? Can you point me in the right ...
3
votes
1answer
181 views
“Changed On/Modified” columns - Date Format & Regional Settings
I was wondering if I change my regional settings through: Site Settings - Regional Settings - Locale: Dutch (Netherlands), affects the date format of the standard columns (Changed On/Modified e.g.).
...
3
votes
2answers
976 views
How to calculate business days excluding Thursdays and Fridays given between two dates?
How to get the business days excluding the weekends (Thursdays and Fridays) in between two given dates? Given dates are two list columns (Start Date & End Date) and business days is a calculated ...
2
votes
5answers
4k views
how to show HTML code in column?
how can i force sharepoint2007 (MOSS) to interpret HTML code instead of showing it?
I have a column which calculates a yes or no (depending on other columns) within a HTML (i want to show images ...
2
votes
3answers
3k views
Creating CASE statement with Calculated Column
I am using SharePoint 2010.
I added a new Column and made it a Calculated Column.
Now I need to place a CASE statement in my Formula. Something like this...
CASE when [Region1] = 'A' and [Region2] ...
2
votes
3answers
78 views
How do I apply a calculated column to new items only?
I have a formula in a column that calculates the target date of + 3 months. I want to change the formula to +9 months but only for new items. How do I add this to the formula?
=IF([Type of ...
2
votes
1answer
860 views
Unescape HTML from list column
I've got a multiline column in a list that contains HTML. When trying to use the field in SharePoint Designer 2010, it shows it as raw HTML, instead of rendering it. So using this syntax:
...
2
votes
2answers
572 views
Create a calculated column to diplay midpoint between 2 dates
Any help greatly appreciated.
I'm using SharePoint 2010, typical TASK LIST. I want to put in a start date and a a due date but have a field (column) that displays the MID WAY POINT between those two ...
2
votes
5answers
2k views
How to add line breaks in calculated columns without going into data view?
Just wondering how to create a list with line breaks in a calculated column without using data view.
2
votes
2answers
1k views
How do I use “Approval Status” in a Calculated Field
I am trying to create a calculated field that will add the text [PEN] to the beginning of the displayed text on my Calendar when the event is still "Pending" according to its "Approval Status".
If ...
2
votes
3answers
689 views
Calculated Field IF expression separators
I'm creating a list with some calculated fields from code that use IF expressions.
fieldCalc.Formula = "=IF([Field1]=0,[Field2],[Field3]&\" (\"&([Field4]/[Field5]*100)&\"%)\")";
...
2
votes
3answers
2k views
Calculated Column colour coded result
I have created a list on our new company sharepoint to track monthly sales orders, a kind of very basic sales pipeline if you will.
In our list we have "Sales Price" and "Cost Price" columns and from ...
2
votes
1answer
3k views
SharePoint List Calculated column to calulate Totals
I could import the excel data shown here to a SharePoint list 2010.I imported all the columns. Let's say I have two columns. Name and Value.
For Value column, I have numeric type. I need to calculate ...
2
votes
5answers
3k views
How do we create dynamic url in Sharepoint 2010 Calculated field
I am working on creating sharepoint dashboard using color coding. I have my custom list created and all of the traffic lights are working based on condition user provides. Following is the basic set ...
2
votes
2answers
1k views
Calculated column based on number of entries of each choice in a choice column
I am working in SharePoint 2010. I need to make a chart to represent how many projects we have from each division. We have a SharePoint list of our projects with a choice column of all the divisions.
...
2
votes
1answer
1k views
Calculated Column - Last Name from Full Name
I'm trying to write a calculated column formula to return the Last Name from a column that currently has employee's full names. The problem that I am having, is that the full names are stored in a ...
2
votes
1answer
316 views
How do you insert a column using the column editor in sharepoint designer 2010?
I would like to create a new calculated column in Sharepoint Designer 2010. So, IN SPD 2010 / Site columns, I click New, Calculated column, fill in the details and click OK. Up comes the the Column ...
2
votes
1answer
38 views
Calculated Value of “Start Time” in a calendar is not working
I am having a problem with a content type-specific, Reusable workflow. I use it for a calendar. When ever an event is created, I send an email to the approver with Name of the event, start time and ...
2
votes
2answers
273 views
Calculated column to Split text
I need to split column into two columns
Column1: SHPS011SSAD
Column2: SH
Column3: PS
What would be the calculated formula for this?
2
votes
3answers
2k views
How to resolve full names from a Person or Group column (that contains multiple entries) in a designer workflow
I have a custom list with a custom content type, with a workflow, on SharePoint 2007 as outlined in this other StackExchange question.
I'm using a Person or Group typed column to contain a list of ...
2
votes
1answer
2k views
How to make SharePoint 2010 calendar view Time Sensitive!
I am trying to create Dataview Webpart for SharePoint Calendar Events to display [Today] items.
I was sucessful on that, my next challenge is how to make it time sensitive. I want to display events ...
2
votes
2answers
684 views
Using SharePoint version number in calculated column?
I'm trying to get the value of column "Version" in a calculated column. I'm not sure if it's a bug with SharePoint 2007 but every time a document is updated, the version number shown in the calculated ...
2
votes
2answers
152 views
calculated field help, moss 2007
I'm trying to build a calculated field with the following condition:
if [field1], [field2], [field3] = approved
set [field4] = 1
if [field1], [field2], [field3] = denied
set [field4] = 2
else
...
2
votes
2answers
969 views
HTML Calculated Column
I'm using the HTML calculated column that's outlined on the pathtosharepoint blog, but I'm wondering, is there any way to create a calculated column, that displays a button/check box or anything that ...
2
votes
1answer
36 views
Color coded background on calculated column using Designer
I am aware of the various methods to colorcode columns (jquery, formula) I choose here to use designer on allitems.aspx on a list (im lazy and its easy).
I have given various columns background ...
2
votes
1answer
114 views
Calculated datetime column returns string;# in search index
We have a calculated column, that results in a date. It's output type is set to DateTime - Date only. When we run the search indexer, it shows up in "crawled properties" as a Text-column, and in the ...
2
votes
1answer
90 views
Calculated field to show the greater of two calculated values
I have some documents that need to be deleted based on the following criteria: one year after termination, or three years after hire date, whichever is greater. I have created two calculated columns ...
2
votes
1answer
3k views
Calculated Column with Nested IF statments
Here is my current formula that works.
="<DIV style='font-weight:bold; font-size:24px; color:"&IF([Project Close A]="",IF(Program="CAPITAL",IF([Project Close P]>=[Current ...
1
vote
2answers
101 views
Default Value for tomorrow at specific time
Say I'm currently using a Date/Time field for workflow purposes, the default value being Today+1. That gives the time at 12:00am tomorrow, which is great.
Is there a way to always give the default ...
1
vote
1answer
370 views
How Can I Perform a weighted average of 2 columns?
I need to set up a number of mini-project sites that have "Task", "Effort (md)" and "%complete" columns. Below these listings I need a weighted total % complete. This is done by multiplying effort x ...
1
vote
1answer
546 views
Can I change a calculated column to a Yes/No column?
I currently have a list created with multiple column and column types. One column is a formula based on the answer of another....I need to change the column to a Yes or No choice instead of relying ...
1
vote
3answers
3k views
URL in calculated column
Is it possible to have "clickable" URL address in (SP 2010) calculated column without any JavaScript code or Designer?
I found the way how to easily make "universal" Document ID links visible for ...
1
vote
2answers
308 views
Sharepoint list If formula
I have a column that needs to return the value of another column based on if the value in a third column = S
1
vote
2answers
191 views
Issue with a Calculated column
I have in a list with multiple content types calculated column with this formula :
=if(
...
