If you try to create a calculated column with a formula containing [Today] or [Me] SharePoint gives the error :
“Calculated columns cannot contain volatile functions like Today and Me”
There are a few reasons you might want to do this. One would be to display how many days left you have to complete something, the other is to only show things added in the last X days or due in the next X days.
There are many pages mentioning this workaround :-
1) Create a dummy column called “Today”, then add your calculated column using [Today] in the formula, finally delete the dummy column. If you need to edit the formula then you have to go back to the start and add the dummy column again.
2) Optionally you can create a calculated column called “My Today” and set its formula to =[Today] using the trick above. Reference [My Today] in your other calculated columns. Thus you reference [My Today] which in turn references [Today]. Then you will not have to add the dummy column back in every time you want to edit formulae.
You then pat your self on the back and head home after another successful day of harassing SharePoint to do what you want, Hurrah!
But the next day you take a look at your list and its wrong. The formula has not updated and its still showing yesterdays value. DOH! SharePoint has made a fool of you after all.
Turns around there is actually a good reason for SharePoint not allowing volatile functions like [Today] or [Me] in calculated columns.
“Calculated fields don’t update themselves until item itself is updated.”
Read that again.
The value of a calculated column is only updated when item is edited and in the mean time you will see incorrect calculations every time you look. In fact, for most uses, this trick don’t give you anything more than just referencing the [Modifed] column.
One workaround to the workaround problem is to write a console app to find all these lists that use the [Today] trick and update them automatically once per day. But this workaround to the workaround problem may not be without its own problems that need to be worked around….
Depending on your need there may be a simpler way – turn the problem on its head!
In the example of using a calculated column to filter out items due in x days or overdue SharePoint will accept [Today] as part of the Filter but will not as part of a calculated column – so give it what it wants
[EDIT- WSS 3 / MOSS 2007 now accepts this]
- Use a filter of [Created] is greater than [Today]-7
(Note – its essential to have no spaces; [Today] – 7 will not work but [Today]-7 does.
WSS 2 / SPS 2003
- Create a column called “Filter 7 Days” and formula as =Created+7
- Then use a filter of “Filter 7 Days” is less than [Today]
See this page for more details – http://blog.pentalogic.net/2008/10/advanced-sharepoint-view-and-filters/
So the calculated column is not dynamic (it will never need updating unless the record itself changes) and SharePoint is happy – but the filter is dynamic and you are happy!
If you want a countdown in your view (i.e. Days left to complete a task) then take a look at this post – http://pathtosharepoint.wordpress.com/2008/08/25/a-countdown-for-tasks-lists/
Shameless plugs
– check out our Highlighter product if you want count-down to Today (e.g. 5 days left, 10 days overdue)
– or check out Reminder if you want “Alert Me” type emails when a task is due soon or overdue.
[EDIT] As it’s been pointed out to me the tone of this article was off from what I intended – the intention is to make clear that this well known and oft mentioned trick does not work as you might expect and to offer alternatives.
| Free SharePoint Calculated Column Cheat Sheet |
| a handy 3 page reference for calculated column functions and formulas. |
 |
Tags: Calculated Columns, SharePoint, Today
Set the record straight how? By claiming you are the know all??? That’s pretty blatant to call something wrong, claim it wont work, and then call someone out for bad advice. Real classy on your part. In fact, my post was the first to do the trick and it was writted for 2003. Get your facts straight before you post off the top of your head. I feel sorry for folks that read your posts and think they are getting the full story. Was MOSS ever mentioned in that article? No!
I’ve been in the SharePoint community longer than most and I have to say I am completely insulted by your post and your opinions.
A lot of people have used the Today trick in 2003 and IT DOES WORK. It DOES NOT do automatic updating. You can do that in MOSS if you like using different methods.
I’d prefer if you stay away from my blog and further mentioning of my name in ANY association with yours.
Hi Mark – Firstly a sincere apology for offending you. The post was not intended how it probably reads and I will be editing it shortly to change the tone.
My intention was to make it clear that this no longer works as see on forums and blogs this keeps popping up again and again and even in new posts aimed at WSSv3 – one of the problems is that it appears to work when you first run it and its only later you find out about the update problem.
Again – apologies if I offended you.
Hi!
I am trying to do this for a future sharepoint installation for a client… the business rule is that list items 30 days past their “start date” have the date field appear highlighted. The filter solution here will not suffice in this case. Is there any point in trying a solution with OOB sharepoint (i have the highlighting currently working” using today as a date field with default value of today)? or is my only option custom back-end code (ie custom web-part )? Thanks in advance.
Conditional Formatting – good question.
I haven’t tried this myself but I think your solution will lie in the no mans land between OOB and custom – DVWP/XSLT and SharePoint Designer
Christophe gives an example of how to create a ‘countdown’ (i.e. you have x days to complete this task) and this could be modified to show days since creted and apply some conditional formatting if > 30.
http://pathtosharepoint.wordpress.com/2008/08/25/a-countdown-for-tasks-lists/
You may also be able to skip using DVWP/SPD by using his HTML Calculated column trick to mix in some javascript (the javascript CAN get the current date dynamically)
http://pathtosharepoint.wordpress.com/2008/10/28/the-html-calculated-column-stay-tuned/
Hes the expert on this sort of stuff and it may make a nice blog post, so perhaps its worth contacting him?
Good luck!
[Comment withdrawn by mutual consent]
[Comment withdrawn by mutual consent]
Ryan, the solution you suggest has actually been published!
http://pathtosharepoint.wordpress.com/2008/11/24/countdowns-a-second-method/
I received it from a reader last week, and adapted it to make it cross-browser.
Note that there is also a post on my blog about the useless “Today” trick:
http://pathtosharepoint.wordpress.com/2008/08/14/calculated-columns-the-useless-today-trick/
Christophe
Christophe & Alexander, nice idea. Many people don’t like using SPD.
I’ve emailed Mr TFForums to make sure he has seen your follow up.
I particularly appreciate your post on this topic. The effect of the timing of the refresh of calculated fields in MOSS 2007/WSS V3 is not obvious. I’ve seen lots of too casually suggested solutions for “show me recently changed docs” that were clearly never tested in MOSS 2007.
As a result, most of the “Show me the documents modified in the last 7 days” views that I’ve seen, including the early ones I wrote, didn’t do quite what people expected. In my case, they worked initially and then started to break as people modified documents. The fact that [Today] is “dynamic” obviously has some important nuances.
It was very difficult to find the reason for this for a long time. I assumed it was a bug and had no solution for it, and even very experienced people seemed to agree at the time.
Revisiting this now, your explanation finally makes some sense of it.
[BTW, I hope Mark eventually accepted your apology for the initial post. I think I can understand the tone somewhat because of how long I’ve struggled with this one.]
[…] http://blog.pentalogic.net/2008/11/truth-about-using-today-in-calculated-columns/ […]
[…] works. It doesn’t – Sharepoint will simply not update the value of [Today] dynamically (see The truth about using [Today] in calculated columns and Calculated columns – the useless [Today] trick for more […]
Hi,
thanks for your solution! It works fine – but alerts seem not to work on this view when an Item automatically moves into my view.
Can you conform that or do the alerts work in your case?
Thank you in advance!
Iden
This is precisely why I have not created my own blog. The minute I post something to which I am proud and works in my environment, somebody somewhere in the world will have a differing opinion and trash my work for all to see. Programmers and developers are the worst about doing this type of thing. So opinionated and selectively puratanical about the supposed “right or best” way the whole world should be doing something…and they do not hesitate to slam each other online at the slightest difference of views. Shameful.
Interesting comment Jason – are you saying that we should never question the validity of others work? That ‘experiments’ don’t need to be reproducible to be considered valid?
I thought these were some of the underlying principals of scientific advancement. If we never question things then the Sun would still be orbiting the Earth!
As for the today trick I would argue that its a little more than a case or ‘working in my environment’ or a difference of opinion.
I have a serious offer to you – prove this works in the way any reasonable person would expect (i.e. you look at the list tomorrow and see valid calculations without any extra jiggery pokery) and I will donate $100 to the charity of your choice.
Ryan, thank you for the details here! It helped me accomplish what I needed to do. I am working on tracking the calibration of our testing equipment and needed a field that calculated the last calibration date and then would determined a ‘Pass’ or ‘Fail’ status.
I tried the aforementioned column workarounds and was unable to get the date check test to work. Your explanation of what SharePoint was recording and doing made complete sense.
To accomplish my goal I adapted your instructions and created a column [CalibrationDue] that calculated the data from the column [CalibrationDate]+365 (our policy for calibrations). Then I created a filter, as you suggested, called CalibrationCheck using the Filter technique; Since the next calibration date was already calculated in M D YYYY format, the filter only need to check for “Is Less Than” [Today].
Now all need to do is create an .ASPX page with a web-part region for list data entry and a another web-part region using this filter to show what equipment requires calibration.
Thanks for explaining What WSS/MOSS is doing with the list data and then describing a solution that doesn’t fight the system–it works with it.
Cheers!
Ryan:
Nice job of showing restraint when responding to the first comment on this post. We’ll all in this together, and I share what was apparently your frustration with the number of posts that parrot each other about how Today works (or more accurately doesn’t work) in SharePoint. I, for one, appreciate your post(s) on this topic.
Andy
@Andy – Thanks! It baffles me how often the [Today] trick keeps appearing even now and that older post even in such highly ranked places such as blogs.microsoft.com are not edited even after dozens of critical comments.
Hi Ryan, I’ve just come across your article and I am very new to SharePoint, so I have very little SharePoint knowledge (also, I don’t have a technical background). I am wondering if there is any way at all just to have a list item show the current date. I’m not trying to do anything other than show the current date. I’ve been working on this for a while and have come across many articles on how to get the current date in relation to showing items that are past due, etc. But I just want a field to constantly show the current date. Is there any way to do this? I’m at my wits end :o) Thanks for any advice!
Hi @Denys – Can you describe what you are trying to do? There are maybe a couple of things you can do here but without understanding a little bit more about what you’re up to its hard for me to say. For example
– if you want to default a field to the current date on creating a new record then thats easy.
– if you want a field to always be the current date when viewing a list then thats not – but not sure why you would want to do that anyway.
Give us a bit of background and I’ll see what I can do.
Hello Ryan,
I also do not have a strong knowledge in sharepoint 2007 MOSS and have been playing around setting up a good solution for us for scheduling work. We want a calendar list view of jobs what we have on for that day. I have tried with the [today] trick and trying to filter by date created which i wasn’t able to get working. Also is it possible when in the calendars week view to be able to show other columns other then Topic and Location.
Hopefully my issues make sense and look forwarding finding a solution.
Thanks in advance
Rocco
Hi Rocco – Sounds like you’re wanting to get a list of all jobs for today and there is absolutely no problem using [Today] in Filters – its just using them in Calculated Columns that can cause trouble.
Using [Today] in filters
Also – if you create a view based on the Calendar type and set the default to Day then it will display the current day automagically.
Finally – if you find that the built in Calendar view doesn’t quite do what you need you may want to check out (blatant plug!) our Planner web part. For example it will allow you to see the different job types on separate lines to ensure that someone is scheduled into each role and easily colour code different job types or people.
http://www.pentalogic.net/sharepoint-products/planner#example-uses
Hi Ryan,
It gave me clear understanding, but when i am trying to implement it i am getting error: I got the error ‘Filter value is not a supported date format’.
Any Idea, how exactly I can solve the problem.
Many Thanks,
Elton
Hi Elton – can you send me a screenshot please?
http://www.pentalogic.net/about/support
[…] are many solutions discussed here and here, some of these solutions involve workflows, jobs, and filtered views. But, we needed […]
Hi Ryan,
This is excellent – I’d used the forums you mentioned earlier to make elaborate checks and create an ‘IsCurrent’ column for our calendar WebPart and only noticed recently (after posting an announcement about it working) that it didn’t work!
This is a reasoned article and I think the tone is spot-on. Mr Kruger seemed unjustifiably wound up by your posting…I guess it must have changed a little since the first draft.
Thanks so much,
Justin/
Thanks for the post. I’m using [Today] in filters, not in calculated columns. This works great to create the views I need in a task list. I do need to calculate the number of days between “Created” and [Today] though. I would like to display the number of days a task has been open in a number column. Any suggestions?
@ Dave – as it happens, the ‘complete’ guide to displaying countdown / countup fields (i.e. number of days since day X) in SharePoint http://blog.pentalogic.net/2011/05/how-to-create-a-countdown-in-a-sharepoint-list/
I have a list with tasks that all have to be finished within a certain deadline. I wanted the list to show me which deadlines were getting closer or were exceeded. After a long search on the internet I found there was no satisfying solution to make a column using the current date.
However, I found out that when you modify the view of a list, you can put [today] in the filter section. This allows you to base the view of your list on the current date. This date will automatically be kept current. (I have a dutch version of sharepoint, so I hope I guessed the used terms right 😉 ).
I made 3 different views of the same list on one page. For all views I edited the ‘list view’ and checked the box which says ‘only show items if’ under the tab ‘filter’ ;
View 1, named: Deadline exceeded:
‘Only show if’
‘deadline’ column
‘smaller than’
[Today]
——————————————
View 2, named: Deadline 30 days:
‘Only show if’
‘deadline’ column
‘bigger than’
[Today]+30
Additionally I placed the different webparts in colored tables (red, orange and green).\
In this way it’s very easy for me to prioritize the different tasks in my tasklist.
I hope this answer will be useful for somebody.
Hi Erik – Thanks for the info, the article above shows similar methods with views showing items that are more than 7 days old.
You may also want to check out our Highlighter and Reminder products that help identify overdue tasks and send out email reminder alerts (see links above).
I faced the same problem with TODAY() being static (or at least only updated when the item itself is updated). I came across this solution, that I think solves it in a decent manner.
1) Create this PowerShell script:
param (
[string]$collection,
[string]$site,
[string]$list
)
Write-Output “Collention: $collection Site: $Site List: $List”
if ((Get-PSSnapin “Microsoft.SharePoint.PowerShell” -ErrorAction SilentlyContinue) -eq $null)
{
Add-PSSnapin “Microsoft.SharePoint.PowerShell”
}
$spWeb = Get-SPWeb -Identity “https://$collection.metroselskabet.dk$site”
$spList = $spWeb.Lists[“$list”]
$spList.Items | ForEach-Object { $_.SystemUpdate() }
2) Use Windows Scheduler to have the script run everyday at 00:01AM
Now you can use TODAY() in your calculated fields and trust that they will actually reflect current day.
Thing to note: By using SystemUpdate() instead of just Update(), the item’s Updated and Updated by fields are left untouched 😀
Solution was inspired by this: https://jimehrenberg.wordpress.com/2014/11/19/how-to-keep-sharepoint-calculated-columns-up-to-date
Regards
Leif
Instead of using [Today], could you use TODAY()-[Due Date] to get the number of days? Or does that have the same problem as the use of [TODAY]? (EG: it will be correct today, but incorrect tomorrow)
@Karen – that will still have the same fundamental problem in that it doesn’t update – tomorrow TODAY will still show yesterday.