Date Formulas in SharePoint Calculated Fields 

Tags: Admin, Pre SharePoint 2010

Microsoft provides a nice compilation of Examples of common formulas, but it does not work as advertised with WSS. I have not tried it in MOSS. Let's say there is a date column named ExpirationDate and an integer named NotificationMonths which represents the number of months before the expiration date that we want the notification in our calculated field NotificationDate.

Simple arithmetic using a date and an integer adds the number of days. If ExpirationDate is 10/31/2009, and NotificationMonths is 6, the formula

=ExpirationDate- NotificationMonths
yields 10/26/2009, but what we really want is 5/1/2009 (4/31 does not exist). The link from the first sentence of this post implies that
ought to do exactly this, but instead it gives the error "The formula refers to a column that does not exist. Check the formula for spelling mistakes or change the non-existing column to an existing column."

The solution is to use braces around the field names:

=DATE(YEAR([ExpirationDate]), MONTH([ExpirationDate])-[NotificationMonths], DAY([ExpirationDate]))

Posted by AndyGett on 16-Oct-08
0 Comments  |  Trackback Url | Bookmark this post with: