1,671 articles and 12,389 comments as of Monday, August 23rd, 2010

Monday, November 9, 2009

Dealing with the [Today] problem in SharePoint Calculated Fields

David PetersenGuest 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.

Dealing with the Today Problem

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”. 

David PetersenGuest 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.

 

Please Join the Discussion

23 Responses to “Dealing with the [Today] problem in SharePoint Calculated Fields”
  1. Iain Munro says:

    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

  2. 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!

  3. Brian says:

    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

  4. David says:

    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.

  5. facundo says:

    same as brian

  6. 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.

  7. Brian says:

    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.

  8. 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.

  9. Jason says:

    same as brian too

  10. Emily says:

    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

  11. 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

  12. Emily says:

    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

  13. Emily says:

    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

  14. Anthony says:

    Good article : thanks. It’s gotten me 90% of the way to solving my problem.

    However, I am in Denmark and the Date ( ) function appears to understand the dates in the EndDate column as US formatted dates.

    Today, the 30th of March (03/30/2010), the date 01/04/2010 (the 1st of April 2010) returns a value of 85 days in the Aging column.
    And indeed, the 4th of January 2010 is actually 85 days from today.

    The value 28/03/2010, expected to return 2 days, actually returns a blank.
    I’m guessing that is because “28″ is not a valid month value.

    I can fix this on my site by going to Site Settings –> Regional and changing the “Locale” value to English (United States).
    Locale is currently set to English (United Kingdom).

    This however, changes all date formats on all lists :-(
    Not optimal.

    Could you suggest a way I could adapt your script to work for me locally?

    • Anthony,

      Denmark is my heritage! Always wanted to go there and look up family that still live there.

      As for your date issue – I’m afraid that I am not sure how to help your date formatting. In the code, the date functions aren’t specific on a format. It uses the standard javascript date object and then parses the date using seconds: For example: var dd = (today-d1)/86400;

      The only thing I can think of is how you have your date field set up in SharePoint. The code line:

      var d1 = Date.parse($(e).text())/1000;

      creates a date object for the date that is stored in the SharePoint date value in the dArray (e).

      So, once you have the d1 date object, we take today’s date (today) and subtract d1. and then
      divide by 86400 (60 seconds * 60 minutes * 24 hours = 86400 seconds per day). That gives us the number of days from Today.

      Hope this helps.

      David

      • Anthony says:

        David,

        thanks for the quick reply.

        I’m only Danish by marriage: but it’s a nice country : you should definitely visit sometime :-)

        As I mentioned in my original mail, changing the way Sharepoint formats date views in the list does seem to solve my problem.
        However, as far as I can see that setting (Site Settings –> Regional –> “Locale” –> English (United States).) can only be set at site level, and not at the level on the individual list. Or am I missing something?
        Setting the format at list level would be the simplest solution, if it is possible.

        Or maybe I could, with javascript, reformat the date, before feeding it to the rest of the script?
        Does that seem like a reasonable approach?

        thanks again :-)

        Anthony

  15. Anthony,

    I believe the problem you are having is related to the line

    var d1 = Date.parse($(e).text())/1000;

    I believe that the javascript Date.parse() function is expecting an American date format. You may want to check out the date parsing function at the following URL: http://www.daniweb.com/code/snippet238772.html . They wrote a date parsing function for European date formats. Once you have parsed the date from the text, then all other parts of the code should work. Try this other date parsing routine and let us know if it worked for you.

    Hope this helps.

    • Anthony says:

      Excellent! Thank you David, it worked like a charm :-))

      There is a small quirk though: it seems to be one day off … it returns “1 day” for a createDate of the 29th (today being the 31st)., for example and ” 2 days” on a createDate of the 28th.

      thanks again :)
      Tony

      • Ivan says:

        Hi Anthony,

        I’m by all means a JS newby and I’m facing the same issue with the date format (DD/MM/YYYY) in Australia. I have tries to include the code from http://www.daniweb.com/code/snippet238772.html but it doesn’t work. Do you mind sharing your solution?

        Thanks,

        Ivan

      • Anthony says:

        Hi Ivan,

        I added the script David referred me to above, in a hidden web part on my list view page.

        On line 90, I added a +1 to calculate the correct number of days.

        var dd = (today-d1)/86400+1;

        Hope that helps ?

      • Ivan says:

        Hi Anthony,

        Thanks for your help, I fixed it. I kept receiving a null value coming from the function at http://www.daniweb.com/code/snippet238772.html. I’m no javascript expert, so I took the part of the function that splits the date in dd/mm/yyyy and create a new variable with the date in mm/dd/yyyy that I parse using the Date.parse function.

        Great script David, thanks a lot !!!!

  16. That’s an interesting problem. Now I wonder if it is in the other date parsing function or if it is still a locale problem. I am not a JavaScript expert by any means. Someone more knowledgeable than me about the JavaScript Date object would be better to answer this question. I suspect that JavaScript gets it’s local information from the machine it is running on. You might check your machine to make sure that the local on your machine is set to European date format. Otherwise, I may have reached the end of my JavaScript Date object knowledge! ;)


Notify me of comments to this article:


Speak and you will be heard.

We check comments hourly.
If you want a pic to show with your comment, go get a gravatar!