Tag Info

Hot answers tagged

9

Me in a Calculated Column I do not understand why you want [Me] in a Calculation. You can use [Me] in de View Filter settings Like the [Created By] and [Modified By] Columns and Lookup Columns [Me] is not available for Calculated Columns as the textual information is not stored in the List Item. I presume its legacy behaviour. Although with todays ...


5

If you only use this for display purposes, why not use a different approach which does not require you to do updates on the list item at all.... Create the site column in a sandboxed solution. Your column stores the Due date. Create a javascript file to deploy somewhere to your site, e.g. /Documents/duedate.js Configure the JSLink property in the site ...


5

By far the easiest method to do this is to simply set the "data type returned from this formula" setting on your column to be "Number". Against all logic, this interprets your formula as HTML. I'm not sure if this works in 2007, but it certainly works in 2010 and 2013. Since there are 10,000 views on this topic and very few up votes, I'm guessing this is a ...


4

The built in [Today] and [Me] values have never worked in calculated columns, all references to this in previous versions were "hacks" as Sane mentions. The best ways to do this would be either via workflow on item creation/edit you update a fields based on the formula you desire or you could try a remote event receiver to handle it.


4

I reformatted the Formula in your original post, you can copy/paste multilines and SharePoint will ignore them. The = was allready spotted; You are also missing a False in your second IF =IF([Course #]="AWR135" ,"Promoting Community Cybersecurity Planning" ,IF([Course #]="AWR136" ,"Essentials of Community Cybersecurity" ,"" ...


3

You can create a view in the list which will show items based on the formula. For example, if you want to see items which have been created during Past 90 days, the formula will be Created is greater than or equal to [Today]-90.


3

You are asking for a No SharePoint Designer and SharePoint 2010 solution. It can be done in a Calculated Column without the use of any other Webparts like documented on the blogs the other answers refer to. The technique is documented at http://www.viewmaster365.com/#/How The cons of this approach are also documented. But it ain't easy; Calculated ...


3

Formula will be something like this: =IF(OR([Column3]="5", [Column3]="6"), [director approval date]-[finance approval date], "-") Here is the reference Example of common formulas


3

The Number trick to display HTML only works in Views, it shows the bare HTML on Forms and in Alerts. Full explanation and pros & cons at: http://viewmaster365.com/index.html#/How For Forms you have to go for Client-Side-Rendering (CSR) BTW Your Formula: =IF(Area="HR" ;"<div><img style='float: left' ...


2

In english version of SharePoint it can be done with this: =MID("Hello(ABC)", 7, 3) There is a nice article: http://yalla.itgroove.net/2012/09/sharepoint-calculated-column-formulas/ A dynamic version would be =MID("Hello(ABC)", SEARCH("(","Hello(ABC)")+1, SEARCH(")","Hello(ABC)")-SEARCH("(","Hello(ABC)")-1)


2

In my experience, this level of complexity is when it's time to try something other than a single calculated column. You can nest calculated columns to do some subtotaling, you can make custom columns that do complex calculations in server side code, or you could have a powershell script come through and calculate the column. Any function with 11 closing ...


2

Nicole, Danny's solution should also work. If you want to keep it in one formula, this should work. =IF(LEFT(SUID,1)=";",IF(ISERROR(RIGHT(LEFT(SUID,INT(FIND("=",SUID)-1)),LEN(LEFT(SUID,INT(FIND("=",SUID)-1)))-1)),"Enter the SUID on Package Pickup",RIGHT(LEFT(SUID,INT(FIND("=",SUID)-1)),LEN(LEFT(SUID,INT(FIND("=",SUID)-1)))-1)),"Enter the SUID on Package ...


2

It is easier if you build your Formula in partials/multiple Calculated Formulas, you can add a maximum of 48 Calculated Columns to a List and you don't have to use everyone in a View or Form. So create a separate Column "NumberStripping" with you Formula RIGHT(LEFT(SUID,FIND("=",SUID)-1),LEN(LEFT(SUID,FIND("=",SUID)-1))-1) That is the part you want to ...


2

You need to use HTML calculate column for this, please have a look at: HTML Calculated Column - Client Side Rendering For SharePoint 2013, simplest way is to use Client-Side Rendering (JSLink property).. Please have a look at following samples: Client-side rendering (JS Link) code samples


2

=[START DATE]+[ETC] + ROUNDDOWN([ETC]/5,0)*2 + IF(WEEKDAY([START DATE])+MOD([ETC],5)>=7,2,0) - ROUNDDOWN(WEEKDAY([START DATE])/7,0) + IF(AND(MOD([ETC],5)=0,WEEKDAY([START DATE])=1),-2,0) + IF(AND(MOD([ETC],5)=0,WEEKDAY([START DATE])=7),-2,0) Here's where I copied this from.


2

You don't need a calculated column for this. Just use your existing [DueDate] column twice in your view filter: Show the items when column DueDate is greater than or equal to [Today] and when column DueDate is less than or equal to [Today]+5


2

You cannot use it as a number. You have to "convert" your value to numeric in order to make the validation, otherwise it will be "false". You can use the VALUE function to convert a text representation of a number, or simply add a number to your value to get it interpreted as numeric. =IF([ChoiceField1]+0<=2, 0, {nested IF if false}) or ...


2

You cannot directly specify today and me in calculated column to set the values. You will have to create columns named Today and Me and then use it in your calculated column. Once you are done with the formula for the calculated column, you need to delete the 2 columns created for this. I think this will make your things work.


1

Create a calculated column and set the return type to Single line of text. If the URL path until Arizona is constant, then add the below formula =LEFT(RIGHT([Column Name],LEN([Column Name])-36),FIND("/",RIGHT([Column Name],LEN([Column Name])-36))-1) Replace the column name and Number "36" based on the number of characters before the text "Arizona".


1

I found the problem - the result of the calculated column was being formatted in Inforpath as text and not as a decimal. For future reference, this can be changed like so:


1

Using the ID is unreliable when using a calculated column, which is why it doesn't appear in the options as a column to use when building a formula. I used a similar technique before and experienced the same issues as you. What I did was convert it to an on creation workflow to set the field value instead. This is much more reliable.


1

You don't actually have a value in your NameAndTitle field; you have a lookup that gets the values from two other fields. Sounds like you want to abstract that again -- a lookup on a lookup? That doesn't work in SharePoint. You have to write the value you want to the NameAndTitle field -- if you have SQL access you can do it with a trigger. Otherwise, you ...


1

Your lastname field must be the title field of the list you have repurposed. This is because the PlaceHolderPageTitle content placeholder on the list's dispform.aspx uses Title field as the default. You can change it easily using SP Designer. Open the Dispform.aspx for the list and make the below replacement on the existing PlaceHolderPageTitle field: ...


1

The next option with you is to create a Workflow using SharePoint Designer. Run the workflow on item create and item update. Change the field FinalDate to a DateTime Create a new workflow and attach it to the List in question In the workflow using DateFunction you can add the value into a variable Use Update Current Item activity and update field ...


1

You will need to have 1 column to store information then another for the calculated value. Using a workflow, you would log the modified date in your first column when the status is set to complete. Then your calculated column would be something like =[Column1] - [Created]


1

It'd be something like this: =IF(ISERROR(RIGHT(LEFT(SUID,INT(FIND("=",SUID)-1)),LEN(LEFT(SUID,INT(FIND("=",SUID)-1)))-1)),"Please Enter the SUID Only on Package Pickup",RIGHT(LEFT(SUID,INT(FIND("=",SUID)-1)),LEN(LEFT(SUID,INT(FIND("=",SUID)-1)))-1)) http://msdn.microsoft.com/en-us/library/office/bb862071(v=office.14).aspx


1

Two options Create a timer job which runs daily and updates the list items Create a workflow which fires daily and updates the list items Option 1 is straight forward and easy to implement. Option 2 don't require any deployment and you can use SharePoint Designer to achieve this, but building it is tricky.


1

A default value formula cannot reference another column because the list form does not support a dynamic recalculation of the default formula. If you need to set a default value based on another column, and still have that user editable, you can use InfoPath to customise the form, or if you don't have that, use Javascript / jQuery to set the field.


1

You need SharePoint Designer. If you have not used it before, it may be a bit daunting. Here are the general steps: Open the site with SharePoint Designer, then open the list. In the Settings page, find the panel for the Workflows and add a new workflow. Edit the workflow and add an action that sets a field's value. You may need a few steps to extract the ...


1

I don't have enough Rep to comment on the other answer but Calculated fields are updated at SAVE time. Your Javascript idea could work if users viewing the list have permissions to update the items, otherwise it might update, or might not update. Since you don't have SPD you don't have many other options but you can try designing a reusable workflow in ...



Only top voted, non community-wiki answers of a minimum length are eligible