locked
How to calculate a date difference in a web part

    Question

  • I have a DataForm web part based on a task list that I'm designing in SharePoint Designer.  I'm trying to insert a column into my Form that will calculate the number of days between the Due Date and Today(), but am having very little luck.  I tried inserting (@DueDate - ddwrt:Today()) into the Insert Formula window, but that's only returning NaN.  I'm also not seeing a DateDiff function or anything of the sort.  Does anyone know how I can accomplish this?
    Friday, April 09, 2010 1:09 PM

Answers

  • What at first seems like a simple question...I've used this template...

    the call:

          <xsl:variable name="zTrendCalc" >
               <xsl:call-template name="DateDiff">
                 <xsl:with-param name="StartDate" select="substring-before(@Effective_x0020_Date,'T')"/>
                 <xsl:with-param name="TodayDate" select="substring-before(@Experience_x0020_Period_x0020_To,'T')"/>
               </xsl:call-template>
            </xsl:variable>

    The templates:

    <xsl:template name="DateDiff">
        <xsl:param name="StartDate"></xsl:param>
        <xsl:param name="TodayDate"></xsl:param>
     
        <xsl:variable name="JulianToday">
          <xsl:call-template name="calculate-julian-day">
            <xsl:with-param name="Year" select="substring(ddwrt:FormatDateTime(string($TodayDate), 1033, 'yyyyMMdd'),0,5)"/>
     
            <xsl:with-param name="Month" select="substring(ddwrt:FormatDateTime(string($TodayDate), 1033, 'yyyyMMdd'),5,2)"/>
     
            <xsl:with-param name="Day" select="substring(ddwrt:FormatDateTime(string($TodayDate), 1033, 'yyyyMMdd'),7,2)"/>
        </xsl:call-template>
        </xsl:variable>
        <xsl:variable name="JulianStartDate">
          <xsl:call-template name="calculate-julian-day">
            <xsl:with-param name="Year" select="substring(ddwrt:FormatDateTime(string($StartDate), 1033, 'yyyyMMdd'),0,5)"/>
     
            <xsl:with-param name="Month" select="substring(ddwrt:FormatDateTime(string($StartDate), 1033, 'yyyyMMdd'),5,2)"/>
     
            <xsl:with-param name="Day" select="substring(ddwrt:FormatDateTime(string($StartDate), 1033, 'yyyyMMdd'),7,2)"/>
        </xsl:call-template>
        </xsl:variable>
     
        <xsl:value-of select="$JulianStartDate - $JulianToday"></xsl:value-of>
      </xsl:template>
     
      <xsl:template name="calculate-julian-day">
        <xsl:param name="Year"/>   
        <xsl:param name="Month"/>   
        <xsl:param name="Day"/>   
     
        <xsl:variable name="JulianDay" select="floor((14 - $Month) div 12)"/>   
        <xsl:variable name="JulianYear" select="$Year + 4800 - $JulianDay"/>   
        <xsl:variable name="JulianMonth" select="$Month + 12 * $JulianDay - 3"/>   
     
        <xsl:value-of select="$Day + floor((153 * $JulianMonth + 2) div 5) + $JulianYear * 365 + floor($JulianYear div 4) - floor($JulianYear div 100) + floor($JulianYear div 400) - 32045"/>
      </xsl:template>

    • Proposed as answer by swirch Friday, April 09, 2010 2:27 PM
    • Unproposed as answer by Mike Walsh FIN Friday, April 09, 2010 5:32 PM
    • Marked as answer by Lu Zou-MSFT Thursday, April 15, 2010 6:36 AM
    Friday, April 09, 2010 2:10 PM
  • Date arithmetic is tricky, regardless the language you are doing it in.  It's pretty easy to get odd or invalid results by just subtracting one date from another.  There is a nice set of date arithmetic XSL templates over at the Microsoft SharePoint Designer Team Blog.

    Check out my post on this: http://mdasblog.wordpress.com/2009/07/22/date-arithmetic-in-sharepoint-dvwps/  The getDayDelta template will do exactly what you're looking for.

    M.


    Marc D Anderson - Sympraxis Consulting LLC - Marc D Anderson's Blog - @sympmarc - jQuery Library for SharePoint Web Services
    • Marked as answer by Lu Zou-MSFT Thursday, April 15, 2010 6:36 AM
    Sunday, April 11, 2010 12:51 AM

All replies

  • What at first seems like a simple question...I've used this template...

    the call:

          <xsl:variable name="zTrendCalc" >
               <xsl:call-template name="DateDiff">
                 <xsl:with-param name="StartDate" select="substring-before(@Effective_x0020_Date,'T')"/>
                 <xsl:with-param name="TodayDate" select="substring-before(@Experience_x0020_Period_x0020_To,'T')"/>
               </xsl:call-template>
            </xsl:variable>

    The templates:

    <xsl:template name="DateDiff">
        <xsl:param name="StartDate"></xsl:param>
        <xsl:param name="TodayDate"></xsl:param>
     
        <xsl:variable name="JulianToday">
          <xsl:call-template name="calculate-julian-day">
            <xsl:with-param name="Year" select="substring(ddwrt:FormatDateTime(string($TodayDate), 1033, 'yyyyMMdd'),0,5)"/>
     
            <xsl:with-param name="Month" select="substring(ddwrt:FormatDateTime(string($TodayDate), 1033, 'yyyyMMdd'),5,2)"/>
     
            <xsl:with-param name="Day" select="substring(ddwrt:FormatDateTime(string($TodayDate), 1033, 'yyyyMMdd'),7,2)"/>
        </xsl:call-template>
        </xsl:variable>
        <xsl:variable name="JulianStartDate">
          <xsl:call-template name="calculate-julian-day">
            <xsl:with-param name="Year" select="substring(ddwrt:FormatDateTime(string($StartDate), 1033, 'yyyyMMdd'),0,5)"/>
     
            <xsl:with-param name="Month" select="substring(ddwrt:FormatDateTime(string($StartDate), 1033, 'yyyyMMdd'),5,2)"/>
     
            <xsl:with-param name="Day" select="substring(ddwrt:FormatDateTime(string($StartDate), 1033, 'yyyyMMdd'),7,2)"/>
        </xsl:call-template>
        </xsl:variable>
     
        <xsl:value-of select="$JulianStartDate - $JulianToday"></xsl:value-of>
      </xsl:template>
     
      <xsl:template name="calculate-julian-day">
        <xsl:param name="Year"/>   
        <xsl:param name="Month"/>   
        <xsl:param name="Day"/>   
     
        <xsl:variable name="JulianDay" select="floor((14 - $Month) div 12)"/>   
        <xsl:variable name="JulianYear" select="$Year + 4800 - $JulianDay"/>   
        <xsl:variable name="JulianMonth" select="$Month + 12 * $JulianDay - 3"/>   
     
        <xsl:value-of select="$Day + floor((153 * $JulianMonth + 2) div 5) + $JulianYear * 365 + floor($JulianYear div 4) - floor($JulianYear div 100) + floor($JulianYear div 400) - 32045"/>
      </xsl:template>

    • Proposed as answer by swirch Friday, April 09, 2010 2:27 PM
    • Unproposed as answer by Mike Walsh FIN Friday, April 09, 2010 5:32 PM
    • Marked as answer by Lu Zou-MSFT Thursday, April 15, 2010 6:36 AM
    Friday, April 09, 2010 2:10 PM
  • swirch:

    Please wait for someone else to propose your reply as an answer.

    Proposing your own posts isn't helpful. Propose good answers from other people but wait for someone else to do the same for your posts.

     


    FAQ sites: (SP 2010) http://wssv4faq.mindsharp.com; (v3) http://wssv3faq.mindsharp.com and (WSS 2.0) http://wssv2faq.mindsharp.com
    Complete Book Lists (incl. foreign language) on each site.
    Friday, April 09, 2010 5:34 PM
  • 'K, thx...wasn't sure how it worked...
    Friday, April 09, 2010 5:36 PM
  • Date arithmetic is tricky, regardless the language you are doing it in.  It's pretty easy to get odd or invalid results by just subtracting one date from another.  There is a nice set of date arithmetic XSL templates over at the Microsoft SharePoint Designer Team Blog.

    Check out my post on this: http://mdasblog.wordpress.com/2009/07/22/date-arithmetic-in-sharepoint-dvwps/  The getDayDelta template will do exactly what you're looking for.

    M.


    Marc D Anderson - Sympraxis Consulting LLC - Marc D Anderson's Blog - @sympmarc - jQuery Library for SharePoint Web Services
    • Marked as answer by Lu Zou-MSFT Thursday, April 15, 2010 6:36 AM
    Sunday, April 11, 2010 12:51 AM
  • Hey Swirch,

    Thanks for the post.  This worked well for me.

    -Blake

    Monday, March 07, 2011 5:32 PM