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