Archive for the ‘Calculated Columns’ Category

Using [Today] in SharePoint calculated default values

Date:September 2nd, 2010 Author:Ryan Tags: , , ,
Category: Calculated Columns Comments:2 ;

Despite the infamous “fake today column trick” still appearing in new blog posts on a monthly basis you can’t use Today in calculated columns in SharePoint.

I’ve seen people assume (not unreasonably) that the same rule must apply with calculated default values used in columns that are not calculated columns as well – but strangely,  you actually can use Today in calculated default values, so I am going to explain why it works and how you might use this.

What is the difference between Calculated Column and Calculated Default value?

A calculated column, erm… calculates values from other columns in your list.

SharePoint - add Calculated Column field

For example if you have an orders list with a Quantity and Price column you can calculate a Grand Total of Price * Quantity.

With a calculated default value you use a calculation to set the default value for a column that is not a calculated column – this is the value that is entered automatically when a new list item is created and may be over-written by the user.

SharePoint - setting a calculated default value

So why may I want a calculation using Today as the default value?

Suppose we have a list containing customer complaints and we have a target to resolve everything in 3 days. Sometimes though for complex queries or if there are public holidays we may give more time. In that case we can set the default value as

= Today + 3

But the user can override that if necessary (public holiday etc).

So why can I use Today here but not in calculated columns?

With a calculated column the calculation is only reworked every time a record is updated. When it’s just viewed then it picks up the saved result from the last edit. This makes perfect sense for most calculated values, but not for values using Today or Me.

So for example if we tried to calculate the days left until a due date we might want to use

Days left = Due Date - Today

If we added the complaint on Monday then it would show that we have 3 days left to do it (Thursday). If we just look at the record on the Wednesday it would still show 3 days (as this was the calculation saved on Monday) when in fact we only have 1 day left to keep the customer happy.

That’s why SharePoint wont let you use Today in calculated columns.

However with a default calculated value the result is only worked out once when the record is being created – it’s understood that what is saved in this field is not updated automatically so there is no problem with using Today.

For a bonus point – you can’t reference other fields in calculations for default values (e.g. Start Date or Order Date) as before a record is being created the fields don’t yet contain values.

What else could I do with this?

The OP in this thread is using it to set default financial years. Suppose that your company’s financial year starts in April then you could use this formula to default to 2009/10 or 2010/11 on or after April.

=IF(MONTH(Today)>=4,"2010/11","2009/11")

or generating the year 20xx/xx notation automatically :-

=IF(MONTH(Today)>=4,YEAR(Today) & "/" & (YEAR(Today)+1),(Year(Today)-1) & "/" & YEAR(Today))

It’s also worth noting that you can use a similar formula in calculated columns – suppose you have an Order Date field and the financial year depends upon when the order was placed then simply replace Today with Order Date.

NB – This has been tested with SharePoint 2007/2010.

How To Use Filters in SharePoint to show items in the current Calendar Month

Date:November 26th, 2009 Author:Ryan Tags: , , ,
Category: Calculated Columns, Filter, SharePoint Ideas Comments:40 ;

Its very easy using the Filter feature of SharePoint views to show only items completed in the last 30 days or items due in the next X days; for example :-

Completed >= [Today]-30

On the Advanced SharePoint View and Filter techniques post Ed asked how you could filter for the current calendar month rather than a rolling 30 day period (for example all tasks due this calendar month).

Like all great questions it seemed simple enough – something like only showing items where the Month and Year of the due date = the Month and Year of the current date.

(more…)

Tip – Showing multiple columns in SharePoint Planner Webpart

Date:October 14th, 2009 Author:Ryan Tags: , , , , , ,
Category: Calculated Columns, SharePoint Planner, SharePoint webparts Comments:1 ;

There may be times when you want to display timeline or category information from more than one list column in a gantt chart. For example in the chart below we have taken information from the “priority” and “title” columns of a list to populate the category labels.

With Planner you can show multiple columns in the Category or on the Timeline of a Gantt chart by using a calculated column to merge fields together.

concat-columns
For example to merge Priority and Title columns together you would :-


  • From your list click Settings > Create Column
  • Set a name such as “Category”
  • Choose Calculated for the type
  • In formula enter (where [Title] etc is the name of the columns you want to merge)
      =[Priority] & " : " & [Title]
  • Uncheck ‘Add to default view’ checkbox if you don’t want this appearing for the normal list view.
  • In Planner you can now select this column.

You can merge multiple columns together, for example :-

   =[Priority] & " : " & [Title] & " - " & [Assigned To]

You can also use SharePoint standard functions and formula (many similar to excel) such as :-

   =Text([Start Time],"mmm-yyyy") " : " & [Title]

Which would prefix the Title with Month and year, like so  October-2009 : Your title here

To display the the duration in days you could use

   =DATEDIF([Start Time],[End Time],"d") & " Days"

See some more examples of common functions

SharePoint Planner Webpart Free Trial Download

SharePoint Reminder Webpart – Setting a variable Due In or Overdue By time

Date:June 12th, 2009 Author:Ryan Tags: , , ,
Category: Calculated Columns, SharePoint Reminder, SharePoint webparts Comments:2 ;

When you normally setup SharePoint Reminder webpart you can specify to send an email alert when a date is Due In X days or Overdue by X days




When column Service Date is due in 5 days




One fairly common requirement amongst users of Reminder is the ability to set variable ‘Due In’ or ‘Overdue By’ times. For example lets imagine the scenario of a company using SharePoint and Reminder to help control the servicing of various pieces of equipment.

(more…)

The … ehem… Truth about using [Today] in SharePoint Filters

Date:November 25th, 2008 Author:Ryan Tags: , ,
Category: Calculated Columns, Filter, SharePoint Ideas Comments:1 ;

Back in the day of Windows SharePoint Server 2 and SharePoint Server 2003 you couldn’t use [Today] in Filters.

For example if you tried to setup a filter like this to show items added in the last 7 days



"Filter value is not in a supported date format"

Created is greater than [Today] - 7

Created is greater than [Today] – 7

You would receive an error “Filter Value is not a valid date” when clicking OK

(more…)

The Truth about using [Today] in SharePoint Calculated Columns

Date:November 22nd, 2008 Author:Ryan Tags: , ,
Category: Calculated Columns, SharePoint Ideas Comments:24 ;

If you try to create a calculated column with a formula containing [Today] or [Me] SharePoint gives the error :

“Calculated columns cannot contain volatile functions like Today and Me”

There are a few reasons you might want to do this. One would be to display how many days left you have to complete something, the other is to only show things added in the last X days or due in the next X days.

There are many pages mentioning this workaround :-

1) Create a dummy column called “Today”, then add your calculated column using [Today] in the formula, finally delete the dummy column. If you need to edit the formula then you have to go back to the start and add the dummy column again.

2) Optionally you can create a calculated column called “My Today” and set its formula to =[Today] using the trick above. Reference [My Today] in your other calculated columns. Thus you reference [My Today] which in turn references [Today]. Then you will not have to add the dummy column back in every time you want to edit formulae.

You then pat your self on the back and head home after another successful day of harassing SharePoint to do what you want, Hurrah!

But the next day you take a look at your list and its wrong. The formula has not updated and its still showing yesterdays value. DOH! SharePoint has made a fool of you after all.

Turns around there is actually a good reason for SharePoint not allowing volatile functions like [Today] or [Me] in calculated columns.

“Calculated fields don’t update themselves until item itself is updated.”

Read that again.

The value of a calculated column is only updated when item is edited and in the mean time you will see incorrect calculations every time you look. In fact, for most uses, this trick don’t give you anything more than just referencing the [Modifed] column.

One workaround to the workaround problem is to write a console app to find all these lists that use the [Today] trick and update them automatically once per day. But this workaround to the workaround problem may not be without its own problems that need to be worked around….

Depending on your need there may be a simpler way – turn the problem on its head!

In the example of using a calculated column to filter out items due in x days or overdue SharePoint will accept [Today] as part of the Filter but will not as part of a calculated column – so give it what it wants

[EDIT- WSS 3 / MOSS 2007 now accepts this]

  • Use a filter of [Created] is greater than [Today]-7

(Note – its essential to have no spaces; [Today] – 7 will not work but [Today]-7 does.

WSS 2 / SPS 2003

  • Create a column called “Filter 7 Days” and formula as =Created+7
  • Then use a filter of “Filter 7 Days” is less than [Today]

See this page for more details – http://blog.pentalogic.net/2008/10/advanced-sharepoint-view-and-filters/

So the calculated column is not dynamic (it will never need updating unless the record itself changes) and SharePoint is happy – but the filter is dynamic and you are happy!

If you want a countdown in your view (i.e. Days left to complete a task) then take a look at this post – http://pathtosharepoint.wordpress.com/2008/08/25/a-countdown-for-tasks-lists/

Shameless plug – check this product out if you want “Alert Me” type emails when a task is due soon or overdue.

[EDIT] As it’s been pointed out to me the tone of this article was off from what I intended – the intention is to make clear that this well known and oft mentioned trick does not work as you might expect and to offer alternatives.

Working Days, Weekdays and Holidays in SharePoint Calculated Columns

Date:November 21st, 2008 Author:Ryan Tags: , , ,
Category: Calculated Columns, SharePoint Ideas, SharePoint Reminder, SharePoint webparts Comments:18 ;

AcmeCorp aim to reply to customer complaints within 3 working days. They plan to use SharePoint Reminder webpart to alert managers when these targets are not being met.

We have a list with the following information

ID Title Date Received Reply Due Reply Sent
1 Order hasn’t arrived Friday 7/Nov/08 Wed 12/Nov/08
2 Order missing items Monday 10/Nov/08 Thursday 11/Nov/08

Once we have this table we could make a View with a filter to only show records that don’t yet have a Reply Sent date (i.e. they have not been dealt with) and then configure a Reminder web part to watch this view and send out emails when the Reply Due date is overdue.

(more…)