I usually test my formulas in Excel. The syntax is the same; if it works in Excel, it’s going to work in SharePoint. (There may be exceptions to this, but I haven’t run into them.)
Simply set up some cells with the values you need and then work out your formula. Once you’ve got it working the way you want it, copy it over to your calculated column and replace the cell references with column names (in brackets).
I’ve seen lots of posts discussing this, but no workarounds. Here’s the situation:
This seems to be an annoying “feature” of how a calculated lookup column is represented in the Datasheet view. We’re running SP1 with all current patches at this point, so I don’t hold out hope of a fix anytime soon.
The best workaround that I’ve come up with is to not calculate the lookup column, but to have a simple workflow which does the calculation and updates the column whenever the item changes. This should work just fine, but since my calculated column is already embedded as a source for many other lists (yes, I inherited a solution, yet again), it’s problematic to make the change.
Anyone seen a better workaround? This really gets in the way if you need to do some wholesale changes on a long list that contains this problematic type of column. I’ll certainly post anything I find that gets around this better.
I’ve frequently been asked if it is possible to display only the current month’s documents in a SharePoint list. There is no obvious way to do this, as the filtering mechanism in a view will not allow you to specify a formula as its criteria.
The trick is to create a calculated column on the list. I like to create a Site Column for this so that it is available to me anywhere in the Site Collection that it might turn out to be useful.
So, create a column (either on the list or as a Site Column), choose “Calculated (calculation based on other columns)” as the type of column, and make the data type “Yes/No”. (This is simply a Boolean column, but Yes and No are the values displayed rather than TRUE or FALSE.) Now all you need is a formula that evaluates to TRUE if your conditions are met, and you’re off and running.
Here’s an example. I created a column called ThisFiscalPeriod, with the formula:
=AND(TEXT(YEAR(Today),”####”)=[Fiscal Year],(TEXT(29*MONTH(Today),”mmmm”)=[Fiscal Month]))
I had already created columns called Fiscal Year and Fiscal Month that were part of the Content Type that I was using in the list. Whenever the current year and month matched the Fiscal Year and Fiscal Month, the formula evaluates to TRUE, and the column will display or can be tested to see if it is “Yes”. (See my earlier post on how to use the Today value in a formula.)
If you wanted to show all of the items that had been modified in the current month, then the formula would look like this:
=AND(TEXT(YEAR(Today),”####”)=TEXT(YEAR([Modified]),”####”),(TEXT(29*MONTH(Today),”mmmm”)=TEXT(29*MONTH([Modified]),”mmmm”)))
Note the neat trick in this part of the formula: TEXT(29*MONTH(Today),”mmmm”). MONTH(Today) returns a number from 1 to 12 which indicates the current month (1=January, 2=February, etc.). Multiplying that number by 29 gives a serial day number that *has* to be in the right month. The following table shows how it works:
Date Month Serial Day Text Month 1-Jan 1 29 January 1-Feb 2 58 February 1-Mar 3 87 March 1-Apr 4 116 April 1-May 5 145 May 1-Jun 6 174 June 1-Jul 7 203 July 1-Aug 8 232 August 1-Sep 9 261 September 1-Oct 10 290 October 1-Nov 11 319 November 1-Dec 12 348 December
I needed to put sets of news articles into buckets in a list so that I could allow users to filter based on the time period in which the articles were published. To do this, I created a calculated column as a Site Column (for reusability).
Column name: Archive Period
Calculation: =IF([Publish Date]<([Today]-365),”Year” & TEXT(YEAR([Publish Date]),”####”),IF([Publish Date]>0,TEXT(29*MONTH([Publish Date]),”mmmm”)&” “&YEAR([Publish Date]),”Publish Date not set.”))
This formula returns a value like “September 2007” (when the Publish Date is within the last 365 days) or “Year 2006” (when the Publish Date is more than 365 days ago) which is used to drive the population of the archive views. (We decided to set up a Publish Date column that could be managed separately from the Created Date or Modified Date.)
SharePoint will not allow you to use the Today column in your calculation. The way to get this to work is to add your own column to the list you are working with called Today. The type of column for your Today column is basically irrelevant. This tricks SharePoint because it “thinks” that you are using your own column. Once you have your calculated column’s formula set up correctly, delete your column called Today and all will work as you wanted. Keep in mind that any time you want to edit the calculated column, you will need to add back your “fake” Today column.
Note also that Excel is a great place to build up these formulas so that you can take advantage of the contextual help, etc. See this post.
