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.

Upgrading Your jQueryUI Custom Theme

jQuery ThemeRollerI have always wondered what would happen if I created a custom theme for jQueryUI using the excellent ThemeRoller tool and then wanted to upgrade my version of jQueryUI. It always seemed like the odds were that the existing CSS and accompanying image files wouldn’t change too much (I’ve found that there’s far less disruption in an upgrade to jQueryUI than there is in an upgrade to jQuery itself), but at a certain point, there were bound to be changes that mattered enough to cause a problem.

I haven’t run into any of those problems, but as I’m carefully upgrading jQueryUI in a client installation, I don’t want to make any egregious mistakes. (The buck stops with me no matter how crappy a tool I’m using works.)

I decided to Bingle to see if there was a converter that some kind soul may have built out there somewhere. Lo and behold, it’s even easier than that. I found a nice little answer over on StackExchange that shows how. Thanks to StackExchange user fbuchinger for that. Gotta love the Internet.

If you open your custom CSS file and scroll down to the second main section, you’ll see something like this:

/*!
 * jQuery UI CSS Framework 1.8.20
 *
 * Copyright 2012, AUTHORS.txt (http://jqueryui.com/about)
 * Dual licensed under the MIT or GPL Version 2 licenses.
 * http://jquery.org/license
 *
 * http://docs.jquery.com/UI/Theming/API
 *
 * To view and modify this theme, visit http://jqueryui.com/themeroller/?ffDefault=Verdana,Arial,sans-serif&fwDefault=normal&fsDefault=1.1em[...SNIP...]cornerRadiusShadow=5px
 */

Assuming that you haven’t customized your theme manually since downloading it (probably a big assumption, actually) this link in the CSS file allows you to go right back to where you started in the ThemeRoller. Simply copy that link and paste it into a browser window and the ThemeRoller will load up your custom theme just as you created it.

My guess is that this would be a bit more tenuous depending on how many versions removed you are from the current one, but I’ve had no problems today. This is also helpful if you create a theme and realize a few days later that you missed that baby blue hover color that only shows up with one of the widgets or something.

[important]I just realized that the “To view and modify this theme” link may show up in different places in the CSS file depending upon your version. Do a search – it should be there somewhere.[/important]

People Want Different Things

Dan Antion (@dantion) did a great post over on his SharePoint Stories blog this morning called People Want Different Things. Dan does one excellent post per week on that blog. They arrive every Saturday morning, and reading it is usually one of the first things I do of a Saturday.

Today’s was a great post, as always, and prompted me to write a comment, much of which turned into this post. Dan’s post hits on something I feel very strongly about: there is no one-size-fits-all concept with SharePoint.

There are many, many people out on SharePointLand who seem to think that there is one way to do any one thing in SharePoint. Usually it’s the way that they know best, and is sometimes the way that they are selling (though sales people in SharePointLand seem to be far more ethical than in the other lands I have visited).

This mindset can apply across many dimensions: development, branding, information architecture, process implementation, you name it. There’s a reason people joke about what I and many other SharePointilists usually say, but the right answer is almost always “It depends.”

In Dan’s post, he gives the three main ways he approaches most of his SharePoint work (I won’t repeat them here, read Dan’s post), and I happen to know that there are favors and variations on the three (the jazz masters have nothing on us SharePointilists) and there are probably a fourth and fifth or more some days, too.

And that’s Dan’s point, I believe. Using SharePoint has some science to it, but to a large degree it’s more art than science. Anyone who marches into a new client, project, or meeting thinking they positively have the answer up front is more than likely wrong. SharePoint is a collaboration platform first and foremost – which can make it frustrating to use in other ways – and building stuff in SharePoint ought to be a collaborative process. I call that collaborative development, and whether you label it with other buzzwords like Agile or not, I firmly believe it’s the best approach.

So it’s not just that People Want Different Things; it goes beyond what any one person likes. It’s what the right answer turns out to be. Sometimes you don’t truly know that answer until you have finished. Of course, work on SharePoint-based solutions should never really be finished, but that’s another another post for a different day.