Tip: Using [Today] in a calculated formula
- Details
- Published: Friday, 01 February 2008 03:00
If you ever tried to use a WSS formula inside a "Calculated Field" inside a cusom list of your MOSS 2007 site using the [Today] property, you will recieve an error that you cannot use variablles like [Today] or [me] in calculated field. The problem is that MOSS 2007 does not want to mess up with filed values that are not set to a specific value.
Still you can use it with a small work around. Follow these steps to do so:
- First create a column (with any type) and call it "Today"
- Create your own calculated data cloumn
- Insert your forumla that contains the [Today] like:
=IF(MONTH([Today])=MONTH([YourDateFiled]),"Value 1","Value 2")
then click OK to create this column - Now click on your "Today" column and then press "Delete" to completely delete this column
- Voila! Here you are, and your calculated field should now return the right result (Value 1 or Value 2)
The workaround plays on the functionality of MOSS 2007 that it is checking first for the values of the fileds inside the Column names first, so when it finds that you have a column called "Today" it will take it OK as it will calculate depending on the data coming from this column. When you delete this column the functionality will not find this column so it will get back to the real value of the property [Today] which is actually today's date.
For more information and examples please review these links:
http://groups.google.com/group/microsoft.public.sharepoint.windowsservices/browse_thread/thread/9814c6dd7bfce2ab/d7eb3113628bf183?q=sharepoint+column+current+date
http://www.sharepointblogs.com/mkruger/archive/2007/06/26/using-today-in-a-calculated-formula-birthday-lists.aspx