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 on 2013-01-09. Visit the post there to read additional comments.

Customizing the Display of a SharePoint 2010 Blog

I’ve changed pages countless times in SharePoint Designer, but this case, where I wanted to make a relatively small change to the home page of a Blog site, proved especially onerous.

In most cases, overriding the default XSL is as simply as editing what’s there and saving your changes.

It would seem that the team that works on blogs doesn’t chat with the rest of the product group, because blogs work differently. Blog pages use an XSL file called blog.xsl, which live in the _layouts folder on the Web Front Ends (WFEs).

On the home page of a blog site (default.aspx), there are several XLV Web Parts, but the main one is the one which displays the most recent posts. All I wanted to do in this case was display a different column instead of Created By (@Author) . Because in many organizations someone different that the author is the person who types the post, we had added a Written By column to the Posts list. The logic was simple: if the Written By column had a value, show it, otherwise show the Created By column as usual.

I wanted to keep the XLV Web Part in place in case we needed to work with it in the future, plus the markup it emits is a bit crufty and I didn’t want to have to replicate it all with a custom DVWP. (In retrospect, this *may* have been easier.)

It should have been pretty easy to make the change, but I went down some rat holes. I don’t want *you* to go down those rat holes, so here’s what ended up working.

Open the page in SharePoint Designer, position your cursor on the XLV Web Part which displays the posts and in the List View Tools section of the ribbon, choose Customize XSLT / Customize Entire View.


This pulls the XSL from blog.xsl into the page, in theory so that we can customize it. However, don’t go down that path; there be monsters. Instead, find the line:

<xsl:include href="/_layouts/xsl/blog.xsl"/>

and Ctrl-Click on the href link to the blog.xsl file. This will open the blog.xsl file in a different SharePoint Designer window for the root of the Site Collection. This is handy, because you now want to save a copy of blog.xsl in a Document Library in the root site of the Site Collection. I usually create a Document Library called XSL anyway to hold frequently-used XSL templates, like the ones I have in my SPXSLT Codeplex Project. I named my copy BlogWrittenBy.xsl.

Close the default.aspx page you opened in the blog site now WITHOUT saving it. Remember? Monsters. Open it again. See, no monsters.

Since we had added a new custom column called Written By, I needed to add it to the ViewFields section so that the XLV Web Part would retrieve values for it:

  <FieldRef Name="Title"/>
  <FieldRef Name="Body"/>
  <FieldRef Name="Author"/>
  <FieldRef Name="PostedByWithDate"/>
  <FieldRef Name="CategoryWithLink"/>
  <FieldRef Name="Permalink"/>
  <FieldRef Name="EmailPostLink"/>
  <FieldRef Name="NumCommentsWithLink"/>
  <FieldRef Name="PublishedDate"/>
  <FieldRef Name="PostCategory"/>
  <FieldRef Name="Written_x0020_By"/>

Then I saved the file. Yes, that customizes (or unghosts) the file. I’m OK with that.

Back to the BlogWrittenBy.xsl file I saved above. I found the section of the XSL where Created By (@Author) is emitted:

<xsl:when test="@Name='Author'"><span class="ms-postfootercolor"><xsl:value-of select="'by '"/></span><xsl:text disable-output-escaping="yes" ddwrt:nbsp-preserve="yes">&amp;nbsp;</xsl:text><xsl:value-of select="$thisNode/@Author.span" disable-output-escaping="yes" /></xsl:when>

and replaced it with this:

<xsl:when test="@Name='Author'">
  <xsl:variable name="ShowPerson">
      <xsl:when test="string-length($thisNode/ &gt; 0">
        <xsl:value-of select="$thisNode/@Written_x0020_By.span" disable-output-escaping="yes"/>
        <xsl:value-of select="$thisNode/@Author.span" disable-output-escaping="yes"/>
  <span class="ms-postfootercolor"><xsl:value-of select="$thisNode/../@resource.wss.ByPrefix"/></span><xsl:text disable-output-escaping="yes" ddwrt:nbsp-preserve="yes">&amp;nbsp;</xsl:text><xsl:value-of select="$ShowPerson" disable-output-escaping="yes" />

This added the conditional logic we wanted.

Next, off to the browser, where I opened the page for editing. In the Tool Pane for the List View Web Part which shows the blog posts, I went to the Miscellaneous section and added a link to my XSL file in the Xsl Link field. In my case, since it was stored in the root of my Site Collection, the link was /XSL/BlogWrittenBy.xsl.

Then I saved the change and voila! Exactly what I wanted.

There are other ways to accomplish what I did here with a deployable feature and managed code, etc. In this case, it was a small farm with only a production environment and a single blog where we wanted to make the change. All you enterprise types just gasped, but this is the reality in many organizations.

Obviously it wasn’t all as simple as what I outline above. Here are some of the things I found:

  • If I edited the XSL in SharePoint Designer (after the Customize Entire View step), my changes worked great in SharePoint Designer,. However, when I went to the browser, I saw the page the same way it had looked before. I believe this is because the blog.xsl file was pulled in anyway, and no amount of jiggling things could get me past that.
  • If I tried to add the XSLLink in SharePoint Designer, which ought to work, it always stripped off the leading / in the link to the XSL file. This meant that the link was invalid and blog.xsl was used again.
  • The sequence I ran through above seems to be the only reliable way to make this work. It’s pretty quick (after 47 tries) and I did it twice as I was writing this. Of course, I had a copy of the original default.aspx file to work with. Never edit the default files without making a copy.

Here are two threads from the MSDN Forums that helped a bit. I didn’t have any luck with the ddwrt:ghost="hide" approach.