Feb 16

Generate calender week with calculate fields and [Today] in SharePoint 2007

In some cases, you want to insert a calculated field with a value depending of today’s date.  By entering the value [Today] into the formular, you get an error like this:

Error Calculated FieldWith Today

This error is caused by a reserved status of the name [Today]. In general, it is only possible to use them for filtering results. But I found a little workaround on the Blog of Chris Johnson. There, he tells us to create a column named »Today«, create a calculated field with a formula using [Today] and delete the first column after this.

In this example, I want to insert a column with the week number of today’s date. According to the linked blogpost, create a column »Today«. It is unimportant, which type you choose.

Create a new Column Today

After this I modified an existing Excel formula, written in the past. So, at the end this snippet can be inserted into the formula field of a calculated field:

=IF(INT(([Today]-DATE(YEAR([Today]),1,1)+(TEXT(WEEKDAY(DATE(YEAR([Today]),1,1)),"d")))/7)+1<=0,0, INT(([Today]-DATE(YEAR([Today]),1,1)+(TEXT(WEEKDAY(DATE(YEAR([Today]),1,1)),"d")))/7)+1)

Now save this field and remove the Today column.

I’m not sure, how this will behave in SharePoint 2010. Has anyone tried this?

Permanent link to this article: http://project89.com/calender-week-calculate-fields-today-sharepoint-2007-119/

Leave a Reply

Your email address will not be published.

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>