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.

Getting Good Answers to Your SharePoint Questions

This post was cross-posted on on 4 May, 2011.

When someone new contacts me through my blog with a question about SharePoint, I’m usually game to help. (Though saying *please* is always a really nice thing. “You have to tell me…” or “give me the answer now…” isn’t so great.)

If you contact me for the first time (at least that I can remember – sometimes it’s not so easy to keep all the real names and handles straight, especially over time), I’m likely to preface my reply with this:

First off, thanks for turning to me with your question.  I’m happy to try to help, but you should really use the MSDN Forums (e.g.,, SharePointOverflow (, or the Stump the Panel ( to get the fastest answers.  I’m very active in each and I’m just as likely to see your question.  Additionally, others can benefit!

It doesn’t mean that I won’t give you a quick answer, but asking your question in the publicly available forums is always a better idea. By doing so, you’re contributing to the corpus of useful content available to *everyone*, including me and your future self. (I can’t tell you how many times I find something I’ve forgotten by Binging and finding my own answer to someone else’s question.)

Here are some additional suggestions. Yes, these are only suggestions. I don’t own any of the forums, of course, nor do I own the Internet, the use of hashtags, or your behavior. Do what you will, but try to be a good Netizen about it.

Do some research before you ask your question. Asking something without doing any work first is sorta lazy. There’s so much content out there about SharePoint that it’s overwhelming, but getting good at using Bing or Google to find answers is a skill which every SharePoint professional must have.

Each of the forums above (plus many others) has a sort of “personality”. I encourage people to try to consider the personality of each forum site for each question they may ask. For instance, if you have a really hard admin question, Stump the Panel is probably not the best place for it.

Another thing I’d suggest is that you choose a channel, post your question, and then BE PATIENT. You’re asking people you may not know to answer your question FOR FREE. Someone will get to it when someone gets to it. *Don’t* post the same question in multiple channels at the same time. If you do post in multiple places, it is very likely to mean that some number of people will waste their time answering a question which has already been answered elsewhere. Instead of posting multiple places, choose one, and tweet the headline with a link to the post, using the #SPHelp hashtag if it’s urgent. That’s going to get you the most eyeballs in the shortest time.

Speaking of the #SPHelp hashtag, I worry that it will become overused, and therefore worthless because of all the noise there will be in the channel. That’s basically what has happened to the #SharePoint hashtag. Recruiters, spammer, and all sorts of people have flooded the channel so much that just following #SharePoint has become sort of a worthless pursuit. In my case, I only watch combinations of the #SharePoint hashtag with other terms, just to screen out the noise. So my suggestion for the #SPHelp hashtag is to use it when you need urgent help, and that’s it. I don’t think it makes sense it add the #SPHelp hashtag to a tweet about a new blog post of yours, for instance, unless you believe that everyone reading the post is going to save endless headaches and is of an extremely time sensitive nature. One instance I can think of might be the CUs which broke SharePoint. That was definitely news everyone could use.

Finally, here’s something I tweeted the other day:

Answering other people’s questions isn’t just a great way to give back to the community; it’s also a great way to learn about SharePoint. I can’t even measure the value I’ve gotten in earning all of the points I have on the various forum platforms. Almost every time I answer a question, even if it is one I’ve answered before, I think about the topic in a new way. If you do decide to answer others’ questions, be sure that you know the answer. Don’t just shoot back with a different question or an observation unless you believe it’ll help solve the problem. Worst of all is to give an answer that is just plain wrong. If you *don’t* know the answer, please, please, please, don’t make something up! There’s actually less of that out there than I used to see, but anything more than none is still too much.

If you aren’t the type of learner that can get as much out of reading and/or answering the forums as I do, consider more formal training. Yes, I’m on the faculty of USPJA and a part owner, so I’m biased, but I think it’s an excellent and affordable way to boost your SharePoint knowledge. There are many other great options out there as well. But try answering questions on the forums. Earn valuable (well, valuable as far as cachet, anyway) points!

MSDN Forum Jam 2010

Better late than never to post about this, I suppose.  Chris Givens set up a great challenge on the MSDN Forums for SharePoint which ended on April 29. You can read about it here and here.  We tweeted about it with the hashtag #forumjam2010.

Once the results were all in, I came in second.  Sure, I was disappointed, but Clayton Cobb was just a machine in pulling in the points. Congratulations, Clayton! I look forward to our next “smack down”.

I came in second, and I’ll take it.  I had to capture Chris’ announcement of it:

Second, Marc Anderson, the King of SharePoint JQuery knowledge out of Boston.  He can make End Users sing love and joy not seen since Woodstock when working with SharePoint and just using the SharePoint Data View and Content Query Web parts!  Although he is not a SharePoint MVP, I have not seen a reason why he is not one.  He has certainly contributed more than I have seen most SharePoint MVPs contribute in, well, let’s be honest, EVER!

Thanks for the kind words, Chris!

For second place, I got a set of Architecting Connected Systems‘ SharePoint 2010 training materials.  I’m making copies as I speak to sell on the InterWebs.  (Just kidding.)  This looks to be excellent stuff, and I’m looking forward to reading it in a spare moment.

image I think this kind of contest should be a more regular thing.  Different people respond to different types of rewards and a contest like this might bring people out to build the SharePoint community more than just the little ribbons that MSDN gives us.  (What’s after five ribbons, anyway? No one can give me a good answer. My motivation is flagging. ;-)  )

I Give My Recognition Some Recognition

I got my fourth little medal in the MSDN Forums yesterday.  I spend time over at the SharePoint Products and Technologies Forums > SharePoint – Design and Customization forum trying to help folks out and because I learn a lot from their questions.  It’s nice to get the little extra GIF for the effort, too.  I only need to double my points to get that last little medal (5 is the max).


A Few Comments (Or Is It a Rant?) on the SharePoint – Design and Customization Forum on MSDN

I’ve been spending quite a bit of time over on the SharePoint – Design and Customization forum at MSDN trying to provide answers to some of the questions there.  Based on many of the questions that I’ve seen, my favorite Data View Web Part (DVWP) continues to be underused, even given its tremendous potential.

Something that I’ve noticed over time is that many people these days (I’m showing my age) often confuse forums with thinking.  This may sound harsh, but in order to be good at doing something like programming, or even just using technology, you need to take what facts you have available to you and think about what you can do with them.  If you get stuck, by all means, try floating it out there on the forums.  There seems to be too quick a tendency to ask questions like "Can anyone give me the exact code to solve my problem?"

I’m a huge fan of teaching people to fish.  If you don’t learn something from each problem you solve, then you’ll never get good at anything.  Call me an Empiricist, and I won’t be offended.  Because of this, many times my "answers" take forms like "Have you thought about…" or "Here’s a similar problem that I solved this way…" rather than "Here’s the answer."

Now, I *love* being able to go out into the blogosphere to find a solution to my problems.  I often do it, especially if I’m pressed or out of my depth.  But even if I find a perfect match for my problem (unusual), I try to dissect the solution so that the *next* time I’ll do a teeny bit better.

Give it a thought.  Constant improvement is a very good thing.  Trust me, you don’t want to be a raving old guy like me *and* not have built up some pretty darn good skills along the way to fall back on.

Watch for my questions and answers in the forum under my handle, sympmarc (short for Sympraxis Marc).