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.
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.
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.
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.
Let me know how it goes. I wish I had known about ddwrt:DateTimeTick in the past.
M.
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.
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
Priyan:
Even though I can’t see your code, I can tell you that the business days calculation is non-trivial.
However, there are some third party XSL libraries you can use, like the one I mention in this post:
http://sympmarc.com/2009/07/22/date-arithmetic-in-sharepoint-dvwps/
Any XSL library with templates that do what you need should work.
M.
Thanks Marc, I have used above mentioned XSLT in my DataView and i got difference in Days only.
How to get difference in format like 7 Days 3 hrs (if hours passed 24 then track days) ?
Hi,
I want to build a form to calculate between start and end date excluding holidays in SharePoint.
Could some one help me on this.
Unfortunately, this is an Ugly Problem. You might want to see if there’s a public Web Service to do it.
M.
Hi there,
Do you have any idea of how I could accomplish this type of expression for conditional formatting in SPD2010?
Yellow Less than 5 days before project “Planned”end date
Green On -Schedule_ up to 5 days before project “Planned”end date
These days I would do this in script with MomentJS. *So* much easier.
M.
This solved a mystery for me. When using a Calculated Column to get the serial date value of a date field, the base date is 1/1/1900. When using SharePoint Designer Workflow, the base date is 12/30/1899. So my workflow dates were always exactly two days off. As you noted, I don’t really care what the base date is, as long as I know how to account for it in the formula.
Thanks Marc!