Hot answers tagged

27

Me in a Calculated Column You can not use [Me] in a Calculated Column Formula. 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 ...


11

Just to be clear: title is only "mandatory" if you create lists using the GUI. When you provision your own list using code or CAML you can create lists without Title. If you insert the formula =Me&" - "&TEXT(Today,"dd-mm-yyyy") into your calculated value, you should get something close to what you want. Be aware though that at any time the user ...


11

Create Calculated column called "UserName" and type in this formula =LEFT([Email Address],INT(FIND("@",[Email Address])-1)) Create Calculated column called "Domain" and type in this formula =RIGHT([Email Address],LEN([Email Address])-INT(FIND("@",[Email Address]))) It should work.


10

If you want the delta values displayed in a View like this: You must create a View that Groups by Room and sorts by Date A Calculated Column 'ChangeMonitor' with Formula set to datatype=Number to output HTML/JS can then use JavaScript code to calculate, display and color the deltas Copy/paste this Formula to your Calculated Column Formula: ="<...


9

Try to insert =[Me] Also look at my answer to a similar post here: Calculated value for Title field?


9

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 ...


9

Unfortunately if you are trying to have this always show the current date and time, this won't work since Calculated Columns are not recalculated on viewing, but only on creation and modification. See this article: http://blog.pentalogic.net/2008/11/truth-about-using-today-in-calculated-columns/ However, if you are just trying to extract the time when ...


8

I think, =ROUNDDOWN([Column1],0) should work nicely. See - https://msdn.microsoft.com/en-us/library/office/bb862071(v=office.14).aspx


7

SharePoint does not support using today's date in a calculated column and the fields are not recalculated unless a value in the item changes. Here is a solution you could try using SharePoint Designer and conditional formatting: Create another calculated column to hold [Due Date]-3 (e.g. Warning Date) Create your [Almost Due] column and put your message ...


7

Rewrite your formula with if-statements and nest them, something like this: =if(AND([Region1]="A",[Region2]="B"),"NO",if(AND([Region1]="C",[Region2]="D"),"YES","N/A")) The first if does the first test, if that statement doesn't hold: use the second if (nested in the first if).


7

Try to use this one =TEXT([DateField];"mmmm"). I usually have error if use ',' instead of ';' in formula. My site language is Russian and it then translates automatically if I write first in English. Reference: Get the month name in calculated column


7

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.


6

elcapitano, my tutorial should work: http://blog.pathtosharepoint.com/2008/09/01/using-calculated-columns-to-write-html/ A couple notes: in the above post, you'll find a link to a troubleshooting page there are several versions of the Text to HTML script, I recommend that you pick the latest one (v 2.1.1) here: http://sp2010.pathtosharepoint.com/...


6

I believe the date/time format you are looking for is: "dd mmm yyyy h:mm AM/PM" This may be useful: TEXT function reference


6

Unfortunately, calculated column approach will not work as expected :( Calculated fields are updated only if the column itself is being updated, or the item is updated. You can fiddle with setting date to future in Windows to prove it. So I would recommend you to use XSL transformations for XsltListViewWebPart in conjunction with dateUtils.xsl from Marc D ...


6

Don't think you can do it with a calculated column, but you can use a list workflow to set a text field. Add a Set Field in Current Item action. Configure the value with User Profiles as data source and select the Name field from source. Then match the Account Name field (only option available) to the column with your User ID (assuming that the user id is ...


6

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 ...


6

To average multiple columns in an item, you'd use a calculated column such as =AVERAGE([Column1],[Column2],[Column3]). To average a column, edit the view and expand the Totals section. By your column name, if it's a number, you can select average (etc) from the dropdown.


5

You can't use "volatile" functions like [Today] in calculated fields. The reason is that the values of calculated fields are only computed when the list item is created or updated, so unfortunately it is not possible to do what you require using a calculated field. You can "trick" SharePoint into allowing a [Today] expression in a calculated field by ...


5

Partly inspired by this question, and partly because we developed a product that has this functionality, I've written a blog post (linked at the bottom) on how to achieve this. The summary is: JavaScript: Use a Content Editor Web Part. Designer: Create a custom view in SharePoint Designer (as already proposed by @Laurie). Code: Make your own custom field ...


5

There's an attribute you add to xsl:value-of called "disable-output-escaping". So change your line to look like this: <xsl:value-of select="@MyColumn" disable-output-escaping="yes"/>


5

You can add a calculated column to your list for [Effort] * [Percent Complete]. Unfortunately, you cannot create a view and select that calculated column for a Sum under Total. You can, however, create a Data View Web Part using SharePoint Designer and add the sum that way. Here is a post on how to Get the sum of a calculated column.


5

In the Default option you can use [Today] plus some number of days... you would have to find the weekday as an integer, compare it to the weekday of Today as an integer, and do some math to get something to add to [Today] to make it next Friday. I'll see if i can find the right formula for it in the mean time. Edit: ok so you can use WEEKDAY([Today]) to ...


5

Below function gives you month in digit. =TEXT([Date],"mm") => returns 01 if date is 15-Jan-2014 Below function gives you month name =TEXT([Date],"mmmm") => returns January if date is 15-Jan-2014 Below function gives you short month name =TEXT([Date],"mmm") => returns Jan if date is 15-Jan-2014


5

There is a calculated column 'trick' where you return a number type but use regular string concatenation and an html 'a' tag and the item will show up for you as a link Something like ="<a href='https://www.google.com?q='"&[searchColumn]&"'>click here</a>" You should be able to add your target _blank attribute using the above. EDIT: ...


5

On the List tab on the ribbon select Modify View. Scroll down and expand Totals section. Select Sum next to Total Miles column and save the view. You can use it together with groups and sorting. Calculated colum I've created calculated column with result type number and set view aggregation from code. It works fine with COUNT (which is also impossible to ...


5

Calculated Formulas are only modified/updated when an Item Changes Read that one more time: Calculated Formulas are only modified/updated when an Item Changes Got it? That means the Today/Now function will get you the result from: the LAST item update. SharePoints Today/Now functions do not behave the same as in Excel. You can force such an update with:...


4

As you google around for this you are going to come across pages telling you you can do this with calculated columns using the [Today] trick. You can't, it doesn't work and isn't updated dynamically - and even if it were [Today] doesn't include a time component so X hrs is out. A couple of options for you Use a CEWP and JavaScript to modify the ...


4

My colleague has written up the different ways to get this sort of countdown/count up display to work in SharePoint, including JavaScript & CEWP SharePoint Designer and DVWP Visual Studio and custom code 3rd party (disclaimer, one of the options is from my own company) http://blog.pentalogic.net/2011/05/how-to-create-a-countdown-in-a-sharepoint-list/


4

I used the following formula. Week starts on monday. Depending on your regional settings you have to replace the ; with , =IF(INT((StartDate-DATE(YEAR(StartDate);1;1)+(TEXT(WEEKDAY(DATE(YEAR(StartDate);1;1)-1);"d")))/7)=0;52;INT((StartDate-DATE(YEAR(StartDate);1;1)+(TEXT(WEEKDAY(DATE(YEAR(StartDate);1;1)-1);"d")))/7))



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