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.
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.
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.
Free SharePoint Calculated Column Cheat Sheet |
a handy 3 page reference for calculated column functions and formulas. |