Calculate Days between Two SharePoint List Dates in XSL Using ddwrt:DateTimeTick

There was an interesting question over in the MSDN Forums the other day that I struggled to answer. It was a headscratcher, so I had to figure it out. In the thread, emfuentes27 wanted to know why s/he saw different results in SharePoint Designer than in the browser when using this formula:

<xsl:value-of select="number(ddwrt:DateTimeTick(ddwrt:GenDisplayName(string(@Due_Date))))- number(ddwrt:DateTimeTick(ddwrt:GenDisplayName(string($Today))))" />

Not only had I never seen the ddwrt:DateTimeTick function (no documentation on that is available anywhere at all that I can find), but the numbers just didn’t make sense.

[important]The ddwrt namespace functions are incredibly valuable, but they are simply not documented by Microsoft anywhere. There is a single article by Serge van den Oever (@svdoever) from the SharePoint 2003 days which explains it (very well), but that’s really it.[/important]

The ddwrt:DateTimeTick isn’t documented there and I’d never seen it in the wild. At least now I know about it.

To determine the days between two dates in the past, I’ve always used date arithmetic XSL templates, as I explain in my post Date Arithmetic in SharePoint DVWPs. The ddwrt:DateTimeTick function turns out to be a lot easier to use, but as I said, the numbers just didn’t make sense. They didn’t make sense, that is, until I went back to a little arithmetic.

It seems that the values in the browser were off from the ones in SPD (I tested this in WSS 3.0 because I had that VM open) by a factor of 864000000000. Trying to figure out the significance of that, I realized that it’s the number of seconds in a day times 1 million:

864000000000 = 60 * 60 * 24 * 1000000

60 * 60 * 24 = the number of seconds in a day.

Wolfram Alpha helps with this. Try going there and typing in 86400 seconds.

Who knows why this is the case, but this equation will give you the right answer in a browser (it will be wrong in SPD), which is what you are really after in the first place:

<xsl:value-of select="(number(ddwrt:DateTimeTick(ddwrt:GenDisplayName(string(@Lead_x0020_Date)))) - number(ddwrt:DateTimeTick(ddwrt:GenDisplayName(string(@Created))))) div 864000000000" />

The ddwrt:DateTimeTick function is a great tool for your XSL work in SharePoint. It returns the number of “ticks” since Dec 30, 1899 in SharePoint Designer. That’s an odd date to use as the base, but you can test it by adding this to your XSL:

1899-12-30::<xsl:value-of select="ddwrt:DateTimeTick(ddwrt:GenDisplayName(string('0001-01-01')))" /><br/>
1899-12-31::<xsl:value-of select="ddwrt:DateTimeTick(ddwrt:GenDisplayName(string('0001-01-02')))" /><br/>
1900-01-01::<xsl:value-of select="ddwrt:DateTimeTick(ddwrt:GenDisplayName(string('0001-01-03')))" /><br/>

Even odder, the base date in the browser seems to be Jan 1, 0001. You don’t see that date bandied about too often. Go ahead; give it a go:

0001-01-01::<xsl:value-of select="ddwrt:DateTimeTick(ddwrt:GenDisplayName(string('0001-01-01'))) div 864000000000" /><br/>
0001-01-02::<xsl:value-of select="ddwrt:DateTimeTick(ddwrt:GenDisplayName(string('0001-01-02'))) div 864000000000" /><br/>
0001-01-03::<xsl:value-of select="ddwrt:DateTimeTick(ddwrt:GenDisplayName(string('0001-01-03'))) div 864000000000" /><br/>

This is yet another example where the rendering engine in SharePoint Designer doesn’t yield the same results as what the SharePoint server provides. Don’t expect something like this to be fixed any time soon, though, given Microsoft’s abandonment of the Design and Split View in SharePoint Designer 2013.

We don’t really care what the base date is for zero ticks, of course; we just want to be able to use the values to determine the difference in days between two “ticks”. As long as you use the million-day-seconds trick, all is well.

This post also appeared at NothingButSharePoint.com on 2013-01-09. Visit the post there to read additional comments.

14 Comments

  1. Very interesting. Just a couple of comments:

    You may want to double check the number of zeros you have on 864000000000 in all occurrences in your post. This:

    86400000000 = 60 * 60 * 24 * 1000000

    is not what appears in your actual copy and pasted formula. That has another zero:

    864000000000

    The number used in your actual formula: 864000000000 is the number of “ticks” in a day

    http://msdn.microsoft.com/en-us/library/system.timespan.ticksperday.aspx

    I don’t know for sure what SPD is doing, but I am guessing that the browser is using a localized version of time while SPD is probably using UTC 0.

    Reply
    • Thanks for catching my typos, Doug. I hadn’t run into the concept of “ticks” before, but I figured it was some standard. I just didn’t know if it was some odd standard put forth by Microsoft (no, that never happens) or what. Since there is zero documentation for the ddwrt:DateTimeTick function…

      M.

      Reply
  2. I’ll need to try this out again. I had this exact problem a while back where I had some conditional formatting on an xlst list view which worked fine in SPD but as soon as we viewed the same list in the browser, the formatting went to pot. (to the point where I had to abandon all formatting on that list). I tried all manner of combinations with the DateTimeTick function (including converting each part of the date and using that) – all to no avail.

    Reply
  3. Can I convert the number of ticks to Date/Time? I’m trying to return the date for the past 7 days from today in a dvwp. I plan to get the number of ticks for $Today, subtract 864000000000 x number of days, then convert the ticks back to date.

    Reply
  4. Marc ,
    In an attempt to calculate the date diff , I used the below formula to achieve the req :

    However i now want to get only the business days in the difference (ie: Sat and Sun is not to be included) .

    Is it possible ?

    Regards,
    Priyan

    Reply

Have a thought or opinion?