Date Arithmetic in SharePoint DVWPs
I posted last September about Date Calculations in a DVWP. In that post, I pointed out Andy Lewis’ great post about how to do Filtering and Formatting with Date Values over at the Microsoft SharePoint Designer Team Blog.
I’ve been answering quite a few questions about date arithmetic over at the MSDN forums lately, so I thought an updated post was in order to explain a little bit more about how you can use the templates that Andy provides. Here are the steps I would suggest:
- Download the date_templates.xsl file.
- Put date_templates.xsl into your Site Collection. I usually put my reusable XSL into /Style Library/XSL Style Sheets in MOSS, or I create a Document Library in the root site in WSS.
- Include the templates in your DVWP. Assuming that you’ve used the location above, add the <xsl:import> line into your DVWP immediately below the <stylesheet line, like so:
&lt;xsl:stylesheet xmlns:x="http://www.w3.org/2001/XMLSchema" xmlns:d="http://schemas.microsoft.com/sharepoint/dsp" version="1.0" exclude-result-prefixes="xsl msxsl ddwrt" xmlns:ddwrt="http://schemas.microsoft.com/WebParts/v2/DataView/runtime" xmlns:asp="http://schemas.microsoft.com/ASPNET/20" xmlns:__designer="http://schemas.microsoft.com/WebParts/v2/DataView/designer" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:msxsl="urn:schemas-microsoft-com:xslt" xmlns:SharePoint="Microsoft.SharePoint.WebControls" xmlns:ddwrt2="urn:frontpage:internal"&gt; &lt;xsl:import href="/Style Library/XSL Style Sheets/date_templates.xsl"/&gt;
- Simply call the templates, passing in the appropriate parameters. For example, to call the getDateFromDays template:
&lt;xsl:call-template name="getDateFromDays"&gt; &lt;xsl:with-param name="paramBaseDate" select="@MyDateColumn"/&gt; &lt;xsl:with-param name="paramDelta"&gt;7&lt;/xsl:with-param&gt; &lt;/xsl:call-template&gt;To understand what you need to pass into each template, you’ll need to dive into the date_templates.xsl file a little, but Andy’s documented the XSL well. Each template will require a set of parameters like getDateFromDays above.
I used this date template too for a project Marc helped me with. Here is some code to use the getDayDelta template:
<!-- get the delta in days between the due date and today --> <xsl:variable name="dueDateDelta"> <xsl:call-template name="getDayDelta"> <xsl:with-param name="paramDateA" select="$Today"/> <xsl:with-param name="paramDateB" select="@Due_x0020_Date"/> </xsl:call-template> </xsl:variable> <xsl:value-of select="$dueDateDelta" />This calculates the difference of Today's date with the Due Date. I also had to add this parameter to the top of my page in code view:
Many thanks Marc,
Matt
I have a question about calculating times with this xsl template. My formula above works great when the days are whole but I’m not getting any values when the dates/time being compared are less than 1. So, if I have a StartTime of Jan 1, 2010 @ 12:30 p.m. and the EndTime is Jan 1, 2010 4:00 p.m., I don’t get a value of 0. I’d like to get the decimal, if at all possible. I’ve tried creating xsl:variables and formating the starttime and endtime to a different format, but I haven’t been successful with that. I get the dreaded NaN. Is it possible to get decimals for this calculation?
Matt:
I’m not sure if the templates do time arithmetic. I can look tomorrow for you. The other option is to build a time arithmetic template. It certainly would be easier than dates.
M.
Building a time template would be a bit over my head, so I cheated. I created a calculated column and added some multiplication to it and I’m getting the results that I expect. Now, I’m just going to write an xsl choose a display the calc column when my date time variable doesn’t work… This will break my sum that I’d like to get with each grouping, *I think*, but that’s what excel or calculators are for, right?
I would love to apply this to find out who is out of the office this week. In other words, does the date range overlap with this week?
So this would be my formula (if I could use [today] in my sharepoint column:
=AND([FilterStartWeek]=([Today]-(WEEKDAY([Today],2)-1)))
where:
filterstartweek =[Start Time]-(WEEKDAY([Start Time],2)-1)
and
filterendweek =[End Time]+(5-WEEKDAY([End Time],2))
I think the last two items are still necessary for my scenario…
I need to be sure I capture the vacation that overlaps with the current week.
I saw your comments on your blog with this code:
but I need another parameter, don’t I? Thanks!
oops – forgot to remove the brackets from the code…
xsl:variable name=”DaysDiff” select=”ddwrt:FormatDateTime(string(ddwrt:Today()), 1033, ‘yyyyMMdd’) – ddwrt:FormatDateTime(string(@Created), 1033, ‘yyyyMMdd’)”/
Stefanie:
I think you’re mixing Calculated formulas with XSL templates. Daysdiff is an XSL template which you can use in a DVWP to calculate the difference in days between two dates.
Data arithmetic is notoriously difficult, as there are lots of rules which can apply. Simply subtracting one date from another usually causes some unplanned results, since months have different numbers of days, different organizations have different work calendars, etc.
M.
This is a really great article! I used getDayDelta for a project to get a value on each row however I am not able to figure out how to create a summary of the total number of dates for all the rows. Any thoughts? Thanks!!
Kevin:
Where there’s XSL, there’s a way! (Usually.) I’d encourage you to post your code over at EndUserSharePoint.com’s Stump the Panel in the SharePoint Designer and the Data View Web Part forum so that we can help you work through this. (Posting code here on WordPress comments is a bit of a PITA.)
M.
Hey, Kevin:
Did you get the summarization to work? I also need this calculation as well. I use the getDayDelta to obtain a Duration in days from Create Date to Completion Date. Now, my manager wants to get an average of Duration. Help!
Tony
Tony:
See my comment above. If you post your XSL over at EUSP, it’ll be easier to help you.
M.
Thanks Marc for sharing this at the first place
Thanks Matt B for suggesting the difference between dates. it worked like a charm.
Does this work in Sharepoint 2010 as well? We’re int he process of moving off 2007 to 2010.
We have a list which includes a DueDate column, basically when an action is due by. We want to display graphical alerters when we’re 21 days (Amber) and 28 days (Red) past the due date .
One of the managers found the code at: http://social.msdn.microsoft.com/Forums/en-US/sharepointcustomization/thread/aeda905b-9bc6-40c4-bd22-21306c5cb0d2/ which we then call with:
images/red.png
images/Anb.png
images/green.png
but it doesn’t appear to work.
Our dates are all UK format (dd/mm/yyyy) but that template seems to interpret any date that woudl be legal in US format (mm/dd/yyyy) as US format but switch to UK format if the date is only legal in UK format. So 08/07/2011 (8th July 2011) would be interpreted as 7th August 2011 but 30/06/2011 is correctly interpreted as 30th June 2011. Obviously if DueDate is 30/06/2011 and we try to subtract that from CurrentDate (which is 08/07/2011) the interpretation US vs UK format has quite an impact on the results.
Unfortunately we are total neophytes when it comes to XSL (dropped in at the deepend with no training and expected to learn as we go along, there’s a reason why I call my blog Sharepoint BY dummies). Do you think that this will solve out problem or could you point us to something that will help us?
Thanks
Stephen
Sorry, pasting of code didn’t work out how I thought it would:
<xsl:variable name="DueDateDiff">
<xsl:call-template name="DateDiff">
<xsl:with-param name="StartDate" select="@DueDate"></xsl:with-param>
<xsl:with-param name="TodayDate" select="ddwrt:TodayIso()"></xsl:with-param>
</xsl:call-template>
</xsl:variable>
<img alt="Indicator" >
<xsl:attribute name="src">
<xsl:choose>
<xsl:when test="$DueDateDiff < -28">images/red.png</xsl:when>
<xsl:when test="$DueDateDiff <= -21">images/Anb.png</xsl:when>
<xsl:otherwise>images/green.png</xsl:otherwise>
</xsl:choose>
</xsl:attribute>
</img>
Stephen:
This XSL approach should work exactly the same in 2010 as it does in 2007.
As for the UK/US date format difference, I’m really not sure. I haven’t dug into Andy’s date templates in quite a long time, but I would think you could adapt them to work with UK date formats.
M.
Marc,
thank you.
It looks like the fundemental problem is the date format misinterpretation and impacts on other things (e.g.; we tried comparing two dates in conditional formatting, straight DueDate <= [CurrentDate] type stuff, and got the same issue). We'll be talking toi the server administrators to see if they can shed any light.
Thanks
Stephen
Stephen:
IMO, the server admins won’t have a clue on this. It’s really Middle Tier development, which most admins eschew.
M.
I’m trying to implement this in SP 2010 and getting errors. Template ‘getDayDelta’ does not exist in stylesheet”
I can click on the link to date_templates.xsl in SPD – code view and it open the file so i know it’s linked
Randy:
It’s hard to say exactly without seeing your environment, but usually the issue is the references. Try using a reference from the root rather than relative to see if that gets it working.
M.
Randy:
It’s hard to say without seeing all of your XSL, of course. I know that you tried to post it in other comments, but code doesn’t come through on WordPress comments. Why don’t you post it at http://sharepoint.stackexchange.com and ping me the link?
M.
Thanks, I think i may have found the issue. The title of this blog says DVWPs. Well I’m trying to use this inside a XLST ListViewWeb. I like/need the advantages of the ribbon support of the XSLTlistView vs the DVWP.
By chance, Is there any way to get something like this to work on the XSLT LVWP?
Randy:
I haven’t tried to add those templates to an XLV, but the process should be basically the same.
M.
I find it to be very different, give it a try and let me know how you make out.
What I am trying to accomplish is identify when the modfied date of a record is current to 6 days old, 7 to 13 days old and then older than 13 days.
is there an easy way to do this?