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. |
 |
Tags: Calculated Columns, SharePoint, Tip, Today
The link is incorrect “That’s why SharePoint wont let you use Today in calculated columns.” Two addresses in one link 😉
Opps! Fixed it now – cheers @Max!
Thanks! Is there a solution, perhaps a macro, or code in SD, that can make this work? (like send current date to a field. Any recommendations appreciated; your site has been extremely helpful.
Bonnie – erm, you can use [Today] in calculated default values – just not calculated fields, is that what you’re asking about?
If so then see the link in the first sentence + also this blog post – http://blog.pentalogic.net/2011/05/how-to-create-a-countdown-in-a-sharepoint-list/
I have used the Today trick and created a calculated column from this:
=today
And I am trying to dynamically calculate start and end days of the month and creating dummy hidden columns in share point for using the calculated column of =(Today). Trouble is I do not think this date for start and end of month updates automatically at end of month. When I create the calculated date column the SP calculates the date for the current month but not the new month when it arrives. Even in existing records I need the value to update to the new month. Is there a way of doing this so it updates automatically. I noticed it gives the correct start and end date for new months but the old records still have the previous start and end of the month.
Any help would be much appreciated.
@Andrew – The *very first* sentence of this post tells you that the ‘Today trick’ does not work. Excuse my frustration – this trick is like a damn Zombie, it won’t die! 😉 See this link for details http://blog.pentalogic.net/2008/11/truth-about-using-today-in-calculated-columns/
Instead if you want to do filters like “Current Month”, “Previous Month” (which am assuming you’r doing from your post) then you have to turn the problem on its head – see this page for a solution – http://blog.pentalogic.net/2009/11/howto-filter-items-current-calendar-month-view-sharepoint/ – hope this helps.
Hi
I am aware that this Today value cannot be used successfully in calculated columns hence the question, but I initially started designing with a sort of overall view that you could “get away with it”. I am new to SP so please excuse the lack of knowledge.
I would like to also use this opportunity – before I go any further – to ask how I can make a list of dates (in date format) for a column on the SP list, I am currently using a look up. I think in all honesty I need to get this before I do anything because this is the main user field for date changes and so it is going to be hard to create a comparison with a string and a date value.
Our list of dates is currently linked from Access and we cannot get the dates to display as a look up list in SP if we set the dates to a date/time field; the date/time field does not show in the ‘column to display’ when setting up the date list; we have to use text dates and that is no good for comparison.
@Andrew – Ignore my grumpiness, just been trying to kill that ‘trick’ off for a while now!
Date fields as a lookup list – why you would want to do that? Why not do them as “Date and Time” columns, is it because you need to limit the dates that the user can display?
Is this you on StackOverflow? – http://stackoverflow.com/questions/8407996/sharepoint-2007-creating-date-look-up-field
A few suggestions for you are
a) A better place for this question (assuming you’re on stack overflow above) is actually http://sharepoint.stackexchange.com/ as stackoverflow is just about solutions involving code – many ways to do things don’t use code and stack-overflow isn’t really the right place.
b) Take a step back and explain as simply as you can what you’re wanting to do in broad terms (rather than at the moment which is half way down a certain path) – there may be a better way to do what you’re wanting to achieve but at the moment its hard to see past the details to the bigger picture.
Let me know if you do post another Q!
Hello Ryan,
I’ve been trying to play around with the Today formula in Sharepoint for some time, however I’m still not having any luck, do you have a suggestion for the below?
I currently have a column within my sharepoint site that lists dates that I received a request on. I’m looking to create a column that calculates how many days it’s been since that request date. Do you know how I can calculate a column to show how many days or even a view that would filter for requests that are active and over X days old?
Any suggestions are appreciated.
Thanks!
Here are some articles (and a product) that may be of use to you.
How to use [Today] in a SharePoint list:
http://blog.pentalogic.net/2011/05/how-use-today-in-a-sharepoint-list/
Using [Today] in SharePoint filters:
http://blog.pentalogic.net/2008/11/the-truth-about-using-today-in-filters/
Lastly, our product: SharePoint Highlighter, which of course comes highly recommended. 😉
http://www.pentalogic.net/sharepoint-products/highlighter
The reason I mention Highlighter is because it would seem to match your requirements quite well; the Countdown (or Countup) option would allow you to show the number of days since the request was received. You could also add row highlighting for those that have been waiting for a long time.
Hi All,
I am trying to create a list that uses approval workflow process.
Scenario: Requestor submit a new task/ document. The workflow send to Approver A. Once approved, workflow will trigger the next step to Approve B. Once approved, it will update the main list approval process as “Approved”
Problem: On the main list, I have 2 fields that shows when each approver have approved the request. How do I do this? Tried do to a reference using calculated field but it does not work.
Specs: I am using a task list, workflow approval process step in Sharepoint 2007.
Can it even be done?
@Remi – you would set the value of these two fields as part of the workflow. You will be better off asking this question on a forum like http://sharepoint.stackexchange.com/
Can anyboday tell me how to validate a column?
My requirement is Date_of_join column should not be greater than today’s date. How to use the TODAY formula in windows sharepoint services 3.0.
Mean In Date_of_Join column when entered tomorrow’s date it should not accept.
Thanks in Advance.
@Nagaraju,
SharePoint 2010 allows column validation and this would be trivial in that ([Date_of_Join] <= [Today]) but you can't do this in SharePoint 2007/WSS3. One alternative is to create your own custom field type in Visual Studio that implements this logic or you can add javascript onto the form to implement the validation there (but this won't help if someone puts info in via the datasheet view). This post will give you a head start on both methods – http://sharepoint.stackexchange.com/questions/36566/validate-a-sharepoint-list-column-with-regular-expression/36651#36651
By the way – you're better asking these sorts of open questions that don't really relate directly to the blog post in one of the forums – http://blog.pentalogic.net/2011/10/sharepoint-questions-msdn-versus-stack-exchange/
Thank you. it save lot of my time.
Syntax for comparing a date in the List file against a date today back x days
i.e Show me all records from 60 days ago till now
[Today]-60
Also Sharepoint has a 5000 record limit so if your query will return a > than that set the Limit to avoid error