A countdown for tasks lists

A common request for tasks lists is to display the number of days left until the due date. Generally speaking, there’s a need among business users to include today’s date in formulas.

In a previous post I have analyzed a method – the “Today” trick – and shown its limits. In this post I am going to describe a different method, using the Data View Web Part and JavaScript.

The method

The tricky parts are to get today’s date and translate dates into durations (number of days).
For my solution I have chosen to use JavaScript, which provides today’s date through the Date object, and a getTime() method that returns the number of milliseconds since midnight of January 1, 1970.
In SharePoint, dates are stored in number of days, the origin being the end of the year 1899.

Using January 1, 1970 as the origin, I am going to calculate two durations:
– DaysToDue (in SharePoint): number of days until Due Date
DaysToDue = [Due Date] – DATE(1970,1,1)
– DaysToToday: number of days until now
DaysToToday = (Today’s date).getTime() / 86400000
(86400000 is the number of milliseconds in one day)

We can then get the number of days left:
TodayToDue = DaysToDue – DaysToToday

Implementation

If you are not familiar with the Data View Web Part, I recommend that you first read the tutorials mentioned in my previous post.

1/ create your tasks list and add a couple tasks with their due date. Filter the list to only keep non completed tasks, and sort by due date (ascending order).

2/ Add a new column called “Days Left”:
– Select: Settings > Create Column
– Create a column called “Days Left”, with type set as ”Calculated (calculation based on other columns)”
– Enter the formula: =IF([Due Date]=0,”No due date”,[Due Date]-DATE(1970,1,1))
– Click OK

3/ create a Web Part page (Site Settings > Create > Web Part page) and insert your tasks view, including the [Days Left] column. You should get something like this:

4/ open the page in SPD in design mode, right-click on the tasks list and choose “convert to XSLT data view” (see my previous post for more details):

5/ the result, after conversion:

6/ switch to code mode and search for these lines:

<!--Days_x0020_Left-->
<!--Days Left-->
<TD Class="{$IDA0WZ5B}"><xsl:value-of disable-output-escaping="yes" select="ddwrt:AutoNewLine(string(@Days_x0020_Left))" /></TD>

7/ in the above lines, replace this tag:

<xsl:value-of disable-output-escaping="yes" select="ddwrt:AutoNewLine(string(@Days_x0020_Left))" />

With:

<DIV align = "right">
<xsl:variable name="StrDaysLeft" select="@Days_x0020_Left" />
<xsl:variable name="NbDaysLeft" select="number(translate(@Days_x0020_Left,',',''))" />
<script type="text/javascript">
var d = new Date();
var StrDaysToDue = '<xsl:value-of disable-output-escaping="no" select="$StrDaysLeft" />';
var DaysToDue = '<xsl:value-of disable-output-escaping="no" select="$NbDaysLeft" />';
var DaysToToday = (d.getTime() / 86400000);
if (StrDaysToDue == "No due date")
{
document.write (StrDaysToDue);
}
else
{
var NumberOfDaysLeft = Math.round((DaysToDue - DaysToToday)*10)/10;
if (NumberOfDaysLeft >= 0)
{
document.write (NumberOfDaysLeft);
}
else
{
document.write ("OVERDUE! " + NumberOfDaysLeft);
}
}
</script>
</DIV>

8/ Save your changes and see the result in your browser:

A couple notes

- The steps in SharePoint Designer are not detailed as it is not the main purpose of this post. Again, see my previous article for details.
– Your own formulas may differ, depending on your conventions (e.g. due date at midday vs. midnight) and your SharePoint date settings (date only vs. date and time).
– The method just renders the day count on the screen, you cannot use it for sorting or filtering purposes. For sorting, use the [Due Date] column. For filtering, use the default [Today] available in SharePoint filters. Set up the sorting and filtering rules BEFORE doing the XSLT conversion in SPD.
– The value in the [Days Left] column is correct only on your DVWP. In the other views of your tasks list, SharePoint will use the original formula (= [Due Date] – DATE(1970,1,1)). I reused the [Days Left] column to simplify the tutorial, but a better way would be to create a separate column in SPD.
– The reference for today is the user’s local time. If you need the server’s local time instead, SharePoint and the ddwrt namespace provide some functions that will return today’s date. You’ll find a good reference here (written for SharePoint 2003).

A question for the SharePoint Designer experts

I am having a hard time formatting dates and durations in SharePoint Designer. In this example, I was unable to remove the 1000 separator (hence the “translate” trick in my script). Insights on this issue are appreciated!

About these ads

103 thoughts on “A countdown for tasks lists

  1. Hi Denise,

    Use copy/paste to get the complete code. It is not cut off, just hidden because of the page formatting.

    Let me know if it worked for you!

    Christophe

  2. Hi Chris,

    I have tested your code, everything works until the step 5. Once I make the changes of the steps 6 and 7, SharePoint displays and ‘NaN’ values for the “StrDaysToDue” and “DaysToDue” respectively.

    The text that your code displays is from:
    document.write (“OVERDUE! ” + NumberOfDaysLeft)

    is: “OVERDUE NaN”

    Any idea why is it not taking the correct value from the column created (Days left)?

    Thank you
    Miguel

  3. Miguel: is your issue for overdue dates only? And what happens when you have no due date?
    One suggestion: double check that you have no weird formatting with quotes.

  4. Hi Chris,

    The issue is that the Internet Explorer only display one message “OVERDUE NaN” for any type of date (overdue or non-expired date).
    Whenever I save the page, the SharePoint Designer keeps the “” written as "s; wherever needed in your code.

    I did a simple test, displaying the value received from the column (Days_x0020_Left); out of the script:

    For this variable (StrDaysLeft) SPD does not display a value.

    For this variable (NbDaysLeft) SPD displays “NaN”.

    As far as in the column display in SharePoint (Step 2 of your code) everything works fine: OVERDUE! for expired dates and “No Due Date” for non-expired dates.

    Any other ideas?

    Thank you,
    Miguel

  5. The code has been deleted in my last comment.

    The code was:

    xsl:variable name=”StrDaysLeft” select=”@Days_x0020_Left”

    xsl:variable name=”NbDaysLeft” select=”number(translate(@Days_x0020_Left,’,’,”))”

    Thanks,
    Miguel

  6. There is another test I just did: having the line as it was at the begging, before replacing the tag (Step 7).

    I have placed the code back:
    xsl:value-of disable-output-escaping=”yes” select=”ddwrt:AutoNewLine(string(@Days_x0020_Left))”

    there is no value displayed either.

    Is the formula of (Step 2) =IF([Due Date]=0,”No due date”,[Due Date]-DATE(1970,1,1))
    preventing the value to be displayed??

    Thank you for all your help!!
    Miguel

  7. Miguel:
    1/ I have edited my post to make the code easier to grab. Please try again (click on “copy to clipboard”). Make sure you don’t use an intermediary text editor like Word as this would wreck the code.
    2/ I see formatting issues with single quotes in your comments above. Again, double check the formatting.
    3/ if it still doesn’t work, send me your source code by e-mail and I’ll take a look.

  8. Chris:

    I have tested the code the way you explained on your last comment.
    Unfortunately, it keeps giving me the same error message: “OVERDUE! NaN”.

    Whenever I do “copy to clipboard” and paste it in my SPD page the code’s format remains the same.

    I have sent the code to your e-mail address: Christophe@PathToSharePoint.com
    Let me know your thoughts.

    Thank you for all your help!!
    Miguel

  9. Sandeep, XSLT doesn’t provide an easy way to calculate date differences, that’s why I prefer Javascript.

    This may change in the future, when more date functions get added to XSLT.

    [Note: Sandeep sent me his XSLT formula by e-mail, and this is my conclusion of our offline discussion]

  10. Update: Miguel was eventually able to make his code work.

    My sample code applies to a default SharePoint tasks list. You’ll need to adapt it if you use another type of list with a different date format (which was the case for Miguel).

  11. Pei, I recommend that you start with a test on a standard tasks list.
    Then for your specific case give me more information about your list type and the settings of your date column.

    • Hi Christophe,
      What changes to the code would be needed to use your countdown or the countdown 2nd method on a standard list and not a task list?
      Thanks!

  12. now, it just worked, but i have another question…

    say, i created a work, then set target date for 10 days, and “days left” starts counting from 10 days to overdue!… , now I want to send out an e-mail to someone when “Days left” equals to overdue!…. how to make that happen?

    i tried to use sharepoint designer 2007 to create a workflow,

    Conditions: if “Days Left” begins with OVERDUE!
    Actions: then send email to …

    it doesnot work, any ideas? (I don’t speak english very well, hope you know what i meant)

  13. Pei, this is a limitation of this workaround (see my notes in the post). SharePoint doesn’t see “overdue!”, it is only rendered in the browser.
    For your purpose you need to use the “Due Date” column and compare it with Today.

  14. hey, chris

    the problem is, Today is not dynamic, it’s not updating everyday. it’s just like the day you edited the work. unless you update the work everyday, that is too much work. how to solve that?

  15. Hi Chris,

    Thanks for the solution. I tried it but found out that it partially is working for me…

    my original code looks like this

    At first I thought of some incompatibility with the code you have with some settings so I tried to rule it out. What i did was to just modified my original code and separated them between lines

    The result I get is that the New Actions and Settings menu disappers and the data from the list are not showing up (only the first few rows of data are being shown).

    I’m not sure what it is… can you please help me.

    Thanks so much.

  16. sorry. i forgot to put my name on the previous post. Anyway, I think I know what’s happening. when i convert to XSLT dataview. That screws everything up even if i did not touch the code yet. Any idea what to do ?

  17. Mac, my only suggestion is to follow exactly my example, starting with a standard tasks list where you haven’t done any customization. If even so SPD screws up your list then you’ll need to call your support…

  18. hi Chris,

    Thanks. I tried to create a standard task list as you have suggested and it did work without any issues.

    Now, I’m wondering why it doesn’t work for the custom list that I’ve created, even though it is just a straight forward list.

    I think the real issue is when i convert to xslt. Any idea?

    regards,

    mac

  19. Hi Chris,

    I figured it out. It errors out if the Due date field is empty. I had of 4 of them out of the 400 entries i have so I wasn’t able to notice. In any case, on the other simple test I did, I noticed that “no due date” message is shown if you leave the Due date field blank. So there is something in my other list that is causing the issue.

    I do have a new question for you (i hope you don’t mind)… :) Is there anyway to filter or show only those that are overdue or almost overdue?

    regards,

    mac

  20. Mac, same advice as for Pei: directly use the [Due Date] column.
    If you want to identify the items that are overdue or due in less than 2 days:
    1/ create a calculated column [DD-2] = [Due Date] – 2.
    2/ apply the following filters on your view:
    – overdue: [Due Date] <=[Today]
    – items due in less than 2 days: [DD-2] <= [Today]

  21. Pingback: Calculated Column Dynamically Updated (When using [Today]) « SharePoint Knowledge Base

  22. Hi everyone!

    First at all, I apologize for the delay on posting the code and instructions that have worked for me…

    I have followed the implementation as Christopher detailed. I have created a Custom List in SharePoint instead of a Task List – this would be a small difference compare to the code described above which is related to a Task list.

    Find the number of instructions’ changes here, I am following the numbering of Christopher’s explanation:

    For Step 1: create a custom list.
    For Step 2: To be able to get the sequential number of the date 1/1/1970, add columns to the custom list. It means: four different columns,
    a) Due Date – this as normal format, which will have the expiration date of every record
    b) Days Left “=DATE(1970,1,1)”
    c) DueDate “= [Due Date]“, to retrieve the sequential number of the column “Due Date” (first column)
    d) sesenta = [Days left], to retrieve the sequential number of the column “Days Left”

    For the Steps 3, 4, 5 and 6: keep the same instructions.

    For the Step 7: Changed the code, to retrieve and use the values of the columns:

    var d = new Date();
    var StrDaysToDue = '';
    var DaysToDue = '';

    var DaysToToday = (d.getTime() / 86400000);

    if (StrDaysToDue == 0)
    {
    document.write (StrDaysToDue);
    }
    else
    {
    var NumberOfDaysLeft = Math.round((DaysToDue – DaysToToday)*10)/10;
    if (NumberOfDaysLeft >= 0)
    {
    document.write (NumberOfDaysLeft);
    }
    else
    {
    document.write ("OVERDUE! " + NumberOfDaysLeft);
    }
    }

    I hope this will help you as it did for me!!

    Miguel.

  23. The code of the Step 7 is not displaying completely.

    I would send it to Chris, and ask him to post it.

    Thank you,
    Miguel

  24. Follow-up: I have done some additional testing, and my code seems to work fine:
    – for both tasks lists and custom lists
    – for both date formats: date only and date+time

    So at this point I don’t feel the need to publish another code. If you’d like to learn more about Miguel’s solution, feel free to contact me and I’ll forward him your message.

  25. Ben, I took a look at your code and there seems to be two issues:
    – charAt(2) == “/” only works if the month number has 2 digits
    – the formatting will apply to other date columns, not just the due date

  26. Ok, I think I’ve fixed it.

    Instead of (charAt(2) == “/”) it looks for (strDate.split(“/”) == 3) The assumption is that only date fields will have two “/” in them to split into a length 3 array.

    I introduced a variable to hold the number of date columns and an array to hold the column index for each column that the coloring should be applied to. To make the code work now, the user needs to specify intNumDateCols = X (the number of date columns in the view, indexed at 0), and aryDateCols = [a, b, ..., X] (the number of the date columns to be colored, indexed at 0).

    Updated code – http://moblog-banditben.blogspot.com/2008/10/color-coding-dates-in-sharepoint-lists.html

  27. Again, why are we made to jump through hoops to do something that should just come out-of-the-box. Shame on Mickeysoft for releasing SharePoint without allowing us to use a function like Today in Calculated Columns.

  28. Pingback: Countdowns - A second method « Path to SharePoint

  29. Pingback: Compare DueDate with Today in list view « SharePoint JavaScript’s

  30. I am hoping some one can see what I am missing. maybe I am going about it the wrong way. I have a calculated column. it displays days left, overdue or No Followup depening on one of 4 conditions. My output is what I need, but I want to color it. when it is over due I want it to be red, but if I add another div tag my formula errors out. Have I written my formula wrong to incorporate this?

    [Code]=””&IF(OR([Date of Follow-up]=””,[Status]=”Closed – Approved”,[Status]=”Closed – Not Approved”,[Status]=”Cancelled”),”No Follow-up”,IF([Date of Follow-up]<[Today],[Today]-[Date of Follow-up]&” Days Left”,IF([Today]<[Date of Follow-up],[Date of Follow-up]-[Today]&” Days Overdue”,””)))&””[/Code]

  31. I read through this post ans noticed a request for a better solution with the commas. I am not sure it is will work but I recently had a similar issue. Being this is already a calculated field you should be able to try FIXED.

    =FIXED([Column],0,TRUE) – no comma, no decimals
    =FIXED([Column],1,TRUE) – no comma, Rounds the first number 1 digit to the right of the decimal point
    =FIXED([Column],-1,TRUE) – no comma, Rounds the first number 1 digit to the left of the decimal point

  32. I am sorry I must be tired. I did not format it correctly. its working now

    =”[DIV]“&IF(OR([Date of Follow-up]=””,[Status]=”Closed – Approved”,[Status]=”Closed – Not Approved”,[Status]=”Cancelled”),”No Follow-up”,IF([Date of Follow-up]<Today,Today-[Date of Follow-up]&” Days Left”,IF(Today<[Date of Follow-up],”[DIV style='color:red;']“&[Date of Follow-up]-Today&” Days Overdue[/DIV]“,””)))[/DIV]

  33. I am working on MOSS 2007.
    Is it possible to check whether we can track the Task completed status change with date.

    Regards,
    Sreejith

  34. Hi Christophe,

    I was inspired by this article and i successfully implemented dynamic(not useless) calculated column(today() issue) using server DATE not the one with java script..

    Really appreciated!!

  35. Hi Christophe!

    I’m trying to implement your solution, but I can’t get through the part where I am to create the “Days left” column. It won’t accept the formula =IF([Due Date]=0,”No due date”,[Due Date]-DATE(1970,1,1)). It claims that the formula contains syntax errors or that it’s not supported.

    I was thinking this might have to do with me using a swedish version of SharePoint and that I had to change the keywords (i.e. DATE), but it didn’t make any difference.

    I should mention that I’m trying to recreate this for a regular document library, but that shouldn’t be the issue either as I tried this with a tasklist as well but still got the same error.

    Do you have any ideas as to what might cause this?

  36. Hi again!

    Never mind my previous post. It seems to be a regional problem after all as I managed to recreate your solution in an english SharePoint environment.

    I sure wish there were more extensive documentation for languages other than english :(

    But thanks for blogging! I hope to make use of this later when I find the swedish keywords, or the solution to whatever my issue might be.

  37. Sorry for clogging up your thread! I’ll just post this if anyone else has the same problem.

    There are very specific keywords for different languages, and you’ll also need to check if the language you’re using makes use of semicolons instead of colons in the conditionals for computed values… Mine did…

  38. Thanks for your reply Christophe, but I’m afraid that solution might not fit my scenario very well as I’m using a document library instead of a task list. I did try it, but I’m too unfamiliar with javascript to make the necessary changes.

    The idea is to have a library displaying the number of days left before an agreement expires, which is a date stored in the documents.

    I had more success with your example Christophe, I’m now at the point where the “Days left” column properly displayes the number of days since 1970. But now the issue seems to be in the javascript. It seems to be a problem with the “NbDaysLeft” variable. I can print the value of StrDaysToDue successfully, but when I try to do the same with DaysToDue it returns NaN (Not a Number) probably meaning that there’s problems translating with number(translate(@Dagar_x0020_kvar) (“Days left” = “dagar kvar” in swedish).

    In the swedish version of SP, four digit numbers are displayed as “1 000″, instead of “1,000” so I had to change the translate function call to this: (@Dagar_x0020_kvar,’ ‘,”). But it still won’t fly. One other difference which I don’t know if it matters or not is that my tag that got replaced with your code looks a little different. I have no idea what it does, but instead of

    I get:

    I’ve struggled with this for almost two days now and don’t have much hair left on the top of my head… can anyone help me?

  39. Now I’m clogging this up again :( I tried to add apostrophes at the end and beginning of my html-tags so the site wouldn’t be able to interpret them as html. All to no success. Strange. And the site also removed it completely it seems as I can’t find it in the source.

    Another try withouth the lt gt chars:
    Your tag:
    xsl:value-of disable-output-escaping=”yes” select=”ddwrt:AutoNewLine(string(@Days_x0020_Left))”

    My tag:
    xsl:value-of disable-output-escaping=”no” select=”format-number(string(@Dagar_x0020_kvar), ‘#,##0.###;-#’)”

    Feel free to clean up som of my posts if you want :)

  40. If anyone else has this problem, the solution is that the “space” used as a delimiter for certain regional settings is not a space at all. It’s a nbsp.

    So this will work: . Only don’t forget the semicolon (;) after &#160.

  41. Chris,
    I was able to tweak your code and get it to post a days behind schedule ~ (1- % Comp.)*(End Date – Start Date)-(End Date – Today’s Date). However, now I would like to group activities and total them in the group. I have done this before with other lists, but when I use the sum function, it shows NaN. I assume that we are posting text and not a number. Is there a way to fix this?

  42. Hi…I have the same problem as Miguel had…..I m using custom list…so can u guide me what should I do to make it work…?

    THanks a lot for everything,

  43. Christophe thanks for the valuable info. It’s working for me. However I have 1 question:

    (1) I want to apply colors to the output. I am using code as follows:

    document.write(“Overdue”);

    but no output displays. If I remove the style it displays.

    Any ideas? Thanks

  44. Is there anyway to implement this with color coding to show items more than 5 days from being due to be displayed in Green, items due within 5 days yellow, and items past due in red?

  45. Pingback: Making SharePoint work for you | Mssptech's Blog

  46. hey–may be a dumb question—-but can you put this code in a content editor web part and make it do the same thing…..
    or does it only work if you open SPD?

    am just starting to use SPD and it makes me nervous that i could completely jack up something else very easily…..

    thanks!

  47. How would I adapt it for comparing with todays date. I need to inactivate subscription the subscription end date is > today?

    Will appreciate greatly any help.

    • You’ll need to modify the following line:
      document.write (“OVERDUE! ” + NumberOfDaysLeft);
      with your own custom action (for example hide the subscription button).

      For the record, there are other similar solutions on my blog, search for “countdown”.

  48. Hi Christophe,
    Thank for your article. It’s help me a lot and I follow your direction but I have the same problem like Miguel. Can I have his solution? Thanks.

    Dana

    • wow, that was long ago and I haven’t kept track of it… Anyway the solution will be specific to your own language date format.
      I’d recommend that you search my blog for countdown. You’ll find other options, including solutions with regional settings.

  49. Hi Christophe,
    I got it. It’s working very good. I used your solution even I created custom list. Do you know why it didn’t work before? the code corrupted because I copied and pasted it into SPD.
    Thanks a lot. I have just finished my project. :-)

  50. Anyway to do this without SPD? I have it, it’s just not enabled to work with SharePoint directly because of company policy. Thanks.

    Lisa

  51. Pingback: Live demo: countdown/countup « Path to SharePoint

  52. Silly question, but what do you do with the original web part? Can I delete it or is it needed? Why do you have to create a new web part anyway? Can’t you put the code into the original list web part???

    • I am not sure what you mean by “original Web Part”. With this method, you add a Web Part to the page, then modify it. If you are talking about the default Web Part when you create a new list, I would recommend not to tweak that one as you might break your page.

  53. Hi Christophe,

    Your code returns the values I want, but both IE and Chrome are rendering the results above the column header bar. Are you using a specific chrome type for the list view on this web part page?

    Thanks,

    Drew

    • I am not sure what the issue could be. Are you using a specific style on your page?
      Note that you’ll find some more recent solutions for countdown in this blog.

  54. Hi Christophe,

    Adore your website, has been a great help!

    However in this tutorial, I’m stuck on step six. That piece of code doesn’t appear, I’ve even tried searching just for “$IDA0WZ5B”. “Days left” only appears once in my code.

    Could it be something to do with modifying the web part to not show the default view?

  55. Lovely solution, works great.

    Any clue how would I create an average of the countdown (in my case countup) values in the footer of the DVWP?

  56. I implemented one of the solutions that used the Today trick and ran into problems and came across this, which seems to be working great. I just followed the instructions because I am not familiar with the scripting. Is there a way to display just the whole number of days left? I get the impression it has something to do with the comment above about using FIXED but I’m afraid I need more details, can anyone help?

  57. Pingback: Example – Implementing Countdowns in Lists | ryanlimtest

  58. Thanks for the tutorial Christophe, I really appreciate it. I am now trying to make Days Left column to display “Complete” whenever the StatusColumn for the row is “completed”. This is my formula:

    =IF(OR(Status=”Completed”),”Complete”,IF([Due Date]=0,”No due date”,[Due Date]-DATE(1970,1,1)))

    This works initially, however, after adding the javascript code, I get a OVERDUE NaN error. I know that the javascript must be modified for it to display as “complete” although I do not know javascript at all.

    Can somebody help me? Thanks in advance

    • David, there’s a line in the code that says:
      if (StrDaysToDue == “No due date”){document.write (StrDaysToDue);}
      else…

      You need to add another line for your case:
      if (StrDaysToDue == “No due date”){document.write (StrDaysToDue);}
      else if (StrDaysToDue == “Complete”){document.write (StrDaysToDue);}
      else…

      This post is very old (one of the first I published!). I have published other “countdown” solutions since then, and XSLT is now kind of outdated (even if it is still supported).

      • Thanks Christophe. I was unaware that you have posted other countdown solutions.

        I am now using this modified code based on what you posted:

        var d = new Date();
        var StrDaysToDue = '';
        var DaysToDue = '';
        var DaysToToday = (d.getTime() / 86400000);
        if (StrDaysToDue == "No due date")
        {
        document.write (StrDaysToDue);
        }
        else

        if (StrDaysToDue == “Complete”){document.write (StrDaysToDue);}
        else

        {
        var NumberOfDaysLeft = Math.round((DaysToDue – DaysToToday)*10)/10;
        if (NumberOfDaysLeft >= 0)
        {
        document.write (NumberOfDaysLeft);
        }
        else
        {
        document.write ("OVERDUE " + NumberOfDaysLeft);
        }
        }

        Unfortunately, now the Days Left column does not display a value at all for any tasks/rows

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s