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.
