Dealing with the [Today] problem in SharePoint Calculated Fields
Guest Author: David Petersen
What’s The (Share)Point?
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.
The formula that I used can be best illustrated by the following code. I didn’t have to change much.
The changes I made are changing the column name to “Aging” and then the var time calculation to only calculate “days”.
Guest Author: David Petersen
What’s The (Share)Point?
David Petersen is an Independent Consultant, husband and father of 4 girls (including triplets) in Omaha, NE. After college and the USMC, he began his IT career working his way up from desktop support tech to IT Director. His history with SharePoint began in 2001 when he developed a proof-of-concept application on Tahoe Server, which eventually became SharePoint. David now casts the SharePoint vision at many companies in the US. His passion is to help companies fully realize their investment in Microsoft technologies.
Hi David
Thanks for the article. As this was a mixture between what you and Paul did, can you tell me all the fields used, their format and which ones were calculated and with what formula.
Thanks
Iain
Hi Iain,
Hopefully I understood what you want. In Paul’s script, he replaces the value in the column represented by the variable ’str’. I used the column name ‘Aging’. In Paul’s example, his was “Last Updated”. I am assuming that that date was changed at each modification. My problem was the date I wanted to calculate aging on was set by the user. I was trying to figure out how to identify the field of the user set date and then do the aging calculations based on that date. I thought I could find that field similar to the way Paul finds the “Last Updated” field but that proved to be the more complicated solution. The solution I came up with was to make the “Aging” field a field of type “calculated | date” and then the formula for the calculated field was =[End Date] which is the user set date. Pauls script then finds the [Aging] date and calculates the length. Since I only needed days (and not hours and min. like Paul), I modified his script once more to stop the calculating after ‘days’.
Hope this helps. Paul needs all of the credit. My post just illustrates how you can take a solution for one thing and modify it to fit your particular requirement.
Cheers!
I’ve got the code in a CEWP, and my calculated column setup similar to your example, however my “Aging” column is returning a “serial date” (39,814 – from the first of this year).
What am I missing? Looks good otherwise!
Brian
Have you set the [Aging] column as a calculated field with type DateTime and then made the formula to =[EndDate] ? Make sure that the [EndDate] field is also a DateTime.
same as brian
You need to make sure that you have a field called [EndDate] of type DateTime. Then your [Aging] field should be a calculated field of type DateTime (Date Only). Set your calculated formula to =[EndDate].
It should all work then.
Hmmm – something still isn’t quite right. I setup a test library and duplicated your demo as above. My Aging column shows the same as the EndDate column: “10/6/2009″, “8/7/2009″, and “9/15/2009″ – whatever date is entered in the EndDate field.
Did you put the CEWP on the same page as the view? I created the view and then edited the page and added the CEWP on the page. Then inserted the code in the CEWP.
same as brian too
Hi,
My problem seems somewhat similar, although I should warn you that I do not have access to SP Designer (we have a bit of an upside down situation here – the end users know more about MOSS than IT). I want to create dynamic queries from lists and libraries using some variation of [Today}-7; so pulling anything from today’s date and the seven previous days. I have a feeling this has a simple solution, but I just haven’t described it properly in search engines.
I also confess to being a newbie at this and probably in way over my head, but I really appreciate any help you can offer. Thanks!
E
Emily,
I believe this is easier for you than you imagine. If you just want to do queries based on [Today]-7, then you can create a custom view using [Today]-7 as the filter. Fortunately, you can use [Today] in views as filters.
If I am misunderstanding what you want, then let me know!
Dave
Thanks so much, Dave! I was clearly trying too hard and making it way too complicated. I think this will work just fine.
Best,
Emily
Oops, I spoke to soon. I’m actually trying to create a query on the main page that searches across multiple document libraries, not just one. I can’t figure out how to incorporate the custom filter you’ve described in a content query tool part, since they don’t want me to use [today] with any calculation.
Is there a better web part for this, or am I stuck showing the results of the [today]-7 filter for each library? Thanks so much for your help.
Emily