Today I was presented with a problem that I thought should have been easy. Create a calculated field that displayed the number of days that had passed from an [EndDate] field. My first thought was to use a calculated field with a function =[Today]-[EndDate]. When I got that, I was informed that calculated fields could not use [Today] or [Me].
A quick Bing informed me that a number of people had run into this same problem. There were references all over of a hack that would have me create a field called [Today] and then use it in the calculated field and then delete the bogus [Today] field. That was really a hack since it didn’t fully work. That hack only calculated [Today] based on when the item was last modified. It wasn’t dynamic. You could have saved a lot of trouble by just using the [Modified] field.
More searching didn’t turn up any other solutions. I tried various vbscript/excel functions in the calculated field to no avail. Then I started thinking clientside. I figured that if I could identify the fields in the list display, I should be able to manipulate them with jQuery. A quick search turned up a nice little piece of code by Paul Grenier on EndUserSharePoint.com. He has written a series entitled jQuery for Everyone and one of his articles was on Replacing [Today]. In his article, Paul talks about replacing a DateTime field with an Aging calculation. His article calculates a DateTime from the last modification date. That works – but it didn’t work for me. What I needed was to calculate the difference between any date set by the user and the current date. My solution was to create an [Aging] field of type Calculated. The formula that I used in the calculated field was =[EndDate] which is the date I need to calculate the difference on. I then modified Pauls code to use the [Aging] field and I also modified his date calculations to only calculate for the date and not hours or minutes. The result ended up looking like the illustration below. Thank you Paul for your awesome tutorial on jQuery in SharePoint. I learn so much.
8 responses to “Dealing with the [Today] problem in SharePoint Calculated Fields”
I’ve blogged about the problems with the oft repeated Today trick – as you’ve found, doesn’t quite work.
http://blog.pentalogic.net/2008/11/truth-about-using-today-in-calculated-columns/
I have followed the steps mentioned as above..But I am getting “Nan Min”. Please advise me on this.
Thanks for your reply..
You will have to send me more than – “It doesn’t work”, if you want me to help. Thanks for reading!
-Dave
Any way you can share the code you modified?
Hi Dave,
Where can I find the complete code with your changes? I have tried using Pauls Code but unfortunately it only amends the first line of the list to today and not the var str component as directed to in the script.
Thanks
I will try to find a copy of the code. I did a follow-up post on End User SharePoint with the code listing but I just checked and their site doesn’t contain the code either. If I find it, I will update this post.
Thanks for reading
Dave
Hi Dave,
Thanks that would be great if you could upload the full code I would be very appreciative as it will allow us me to identify the age of documentation that has been sent.
Thanks
Pete
Perfect! Thank you!