none
SharePoint Designer 2010 - Determine if today's date is within x days of a start date column using conditional formatting.

    Question

  • I'm using SPD 2010 and Sharepoint Server 2010. 

    Using conditional formatting I'm trying to format a list so that if today's date is greater than 3 days past the start date column a cell will turn red. 

    Comparing two date columns directly (to see if today is after the start date) works well -

    ddwrt:DateTimeTick(ddwrt:GenDisplayName(string($thisNode/@StartDate)))  < ddwrt:DateTimeTick(ddwrt:GenDisplayName(string($Today)))

    But if I add a number it will work in SPD design view, but not on the actual SharePoint site.

    ddwrt:DateTimeTick(ddwrt:GenDisplayName(string($thisNode/@StartDate))) + 3 < ddwrt:DateTimeTick(ddwrt:GenDisplayName(string($Today)))

    I tried converting 3 to ticks -> 8,640,000/day and using that value, but that didn't work either (and doesn't work in SPD design view).

    I can get the formatting to work if I create a column with a calculated date of "StartDate+3" and then compare that directly, however, it doesn't work if the column is not visible and I would rather not create additional columns. 

    Any ideas?

    Thanks for you help.

    Thursday, March 10, 2011 4:31 PM

Answers

  • I was trying to do something similar. The problem with using the ddwrt:FormatDateTime approach is adding or subtracting days over the beginning or end of a month. 20110512 minus 20 days is not the date 20110492. I needed to pull a report of items that were older than today, but not older than two weeks ago. I used some of your code, Bpollard3 to make it work:

    [(number(ddwrt:DateTimeTick(ddwrt:GenDisplayName(string(@End_x0020_Date)))) <= number(ddwrt:DateTimeTick(ddwrt:GenDisplayName(string($Today))))) and (number(ddwrt:DateTimeTick(ddwrt:GenDisplayName(string(@End_x0020_Date)))) >= number(ddwrt:DateTimeTick(ddwrt:GenDisplayName(string($Today))))-12096000000000)]

    That number, 12096000000000, is the number of ticks in 14 days. Here's why it wasn't working for you - Ticks are different in SPD than they are in the browser. In SPD, as in Excel, a Tick is the number of days since Jan 1, 1900. In the browser, a single tick represents one hundred nanoseconds or one ten-millionth of a second. There are 10,000 ticks in a millisecond. Here's how I calculated 14 days:

    Time

    Ticks

    millisecond

    10000

    second

    10000000

    minute

    600000000

    hour

    36000000000

    day

    864000000000

    14 days

    12096000000000

     

    • Marked as answer by Bpollard3 Thursday, September 01, 2011 10:34 PM
    Thursday, May 12, 2011 11:44 PM
  • try

    <xsl:if test="ddwrt:FormatDateTime(string($Today), 1033, d) > ddwrt:FormatDateTime(string(@StartDate), 1033, d) + 3">

    This gets the numeric representation of the day so it should be usable with math operations.

    More on date formatting: http://www.csharp-examples.net/string-format-datetime/


    SharePoint 2010 Extensions - http://sp2010ext.codeplex.com/ My Blog - http://www.withinsharepoint.com Twitter - http://twitter.com/#!/withnsharepoint
    • Proposed as answer by Chris Geier Thursday, March 10, 2011 5:13 PM
    • Marked as answer by Bpollard3 Thursday, March 10, 2011 7:26 PM
    Thursday, March 10, 2011 5:04 PM

All replies

  • try

    <xsl:if test="ddwrt:FormatDateTime(string($Today), 1033, d) > ddwrt:FormatDateTime(string(@StartDate), 1033, d) + 3">

    This gets the numeric representation of the day so it should be usable with math operations.

    More on date formatting: http://www.csharp-examples.net/string-format-datetime/


    SharePoint 2010 Extensions - http://sp2010ext.codeplex.com/ My Blog - http://www.withinsharepoint.com Twitter - http://twitter.com/#!/withnsharepoint
    • Proposed as answer by Chris Geier Thursday, March 10, 2011 5:13 PM
    • Marked as answer by Bpollard3 Thursday, March 10, 2011 7:26 PM
    Thursday, March 10, 2011 5:04 PM
  • Thanks Maarten,

    The code I eventually used was a combination of yours and similar code found in another solution:

    http://social.msdn.microsoft.com/Forums/en-US/sharepoint2010customization/thread/109ae6f2-ea16-464f-978e-b565a69721a5

    The code I used:

    number(ddwrt:FormatDateTime(ddwrt:FormatDate(string($thisNode/@Scan_x0020_Date),1033,1),1033,'yyyyMMdd')+3) <= number(ddwrt:FormatDateTime(ddwrt:FormatDate(string($Today),1033,1),1033,'yyyyMMdd'))

    I'm not sure if everything is necessary or why FormatDateTime and FormatDate were needed, but I was recieving Type mismatch errors without it (maybe you know why?).

    Thanks again,

    Ben

    • Marked as answer by Bpollard3 Thursday, March 10, 2011 7:33 PM
    • Unmarked as answer by Bpollard3 Thursday, March 10, 2011 9:54 PM
    Thursday, March 10, 2011 7:26 PM
  • Good deal. The format date time converts the date time from a string value representing the long date time format of the field in to an integer. As a string the math operations fail as it can't add 3 to a string.
    My CodePlex - My Blog - My Twitter
    Thursday, March 10, 2011 11:47 PM
  • I was trying to do something similar. The problem with using the ddwrt:FormatDateTime approach is adding or subtracting days over the beginning or end of a month. 20110512 minus 20 days is not the date 20110492. I needed to pull a report of items that were older than today, but not older than two weeks ago. I used some of your code, Bpollard3 to make it work:

    [(number(ddwrt:DateTimeTick(ddwrt:GenDisplayName(string(@End_x0020_Date)))) <= number(ddwrt:DateTimeTick(ddwrt:GenDisplayName(string($Today))))) and (number(ddwrt:DateTimeTick(ddwrt:GenDisplayName(string(@End_x0020_Date)))) >= number(ddwrt:DateTimeTick(ddwrt:GenDisplayName(string($Today))))-12096000000000)]

    That number, 12096000000000, is the number of ticks in 14 days. Here's why it wasn't working for you - Ticks are different in SPD than they are in the browser. In SPD, as in Excel, a Tick is the number of days since Jan 1, 1900. In the browser, a single tick represents one hundred nanoseconds or one ten-millionth of a second. There are 10,000 ticks in a millisecond. Here's how I calculated 14 days:

    Time

    Ticks

    millisecond

    10000

    second

    10000000

    minute

    600000000

    hour

    36000000000

    day

    864000000000

    14 days

    12096000000000

     

    • Marked as answer by Bpollard3 Thursday, September 01, 2011 10:34 PM
    Thursday, May 12, 2011 11:44 PM
  • Thanks Dave,

    You're right. I didn't see your response until now. I've just started beta testing my code a month ago and ran into this exact problem today (9/1). Your solution looks like the correct way to do it.

    Thursday, September 01, 2011 10:34 PM
  • I was trying to do something similar. The problem with using the ddwrt:FormatDateTime approach is adding or subtracting days over the beginning or end of a month. 20110512 minus 20 days is not the date 20110492. I needed to pull a report of items that were older than today, but not older than two weeks ago. I used some of your code, Bpollard3 to make it work:

    [(number(ddwrt:DateTimeTick(ddwrt:GenDisplayName(string(@End_x0020_Date)))) <= number(ddwrt:DateTimeTick(ddwrt:GenDisplayName(string($Today))))) and (number(ddwrt:DateTimeTick(ddwrt:GenDisplayName(string(@End_x0020_Date)))) >= number(ddwrt:DateTimeTick(ddwrt:GenDisplayName(string($Today))))-12096000000000)]

    That number, 12096000000000, is the number of ticks in 14 days. Here's why it wasn't working for you - Ticks are different in SPD than they are in the browser. In SPD, as in Excel, a Tick is the number of days since Jan 1, 1900. In the browser, a single tick represents one hundred nanoseconds or one ten-millionth of a second. There are 10,000 ticks in a millisecond. Here's how I calculated 14 days:

      
     

    Time

    Ticks

    millisecond

    10000

    second

    10000000

    minute

    600000000

    hour

    36000000000

    day

    864000000000

    14 days

    12096000000000

     

     

    This did not work for us. I'm trying to highlight columns where "logged on" dates are more than 5 days old and "modified" more than 10 days old.

     

    not($thisNode/@Status = 'Closed' or $thisNode/@Status = '4 - Instant Fix' or $thisNode/@Status != '9 - General') and ($thisNode/@Priority = '5 - Request' and number(ddwrt:DateTimeTick(ddwrt:GenDisplayName(string($thisNode/@Logged_x0020_on)))) > number(ddwrt:DateTimeTick(ddwrt:GenDisplayName(string('Today')))) - 864000000000*5 and number(ddwrt:DateTimeTick(ddwrt:GenDisplayName(string($thisNode/@Modified)))) > number(ddwrt:DateTimeTick(ddwrt:GenDisplayName(string('Today')))) - 864000000000*10)


    Bhavesh
    • Edited by bhav27 Wednesday, January 18, 2012 1:23 PM
    • Proposed as answer by ogabriel Saturday, February 04, 2012 11:50 PM
    Wednesday, January 18, 2012 12:25 PM
  • Here's the solution I've found to solve the problem. In my case I want to determine the number of days elapsed between my column "deadline date" ($thisNode/@D_x00e9_lai) and today's date. I used the LCID 4108 for the Swiss French ONLY convert today's date.

    In this case I wanted to display the date in red between 1 and 7 days after today's date. It works perfectly.

    ((ddwrt:DateTimeTick(ddwrt:GenDisplayName(string($thisNode/@D_x00e9_lai))) - ddwrt:DateTimeTick(ddwrt:GenDisplayName(string(ddwrt:FormatDate(ddwrt:Today(),4108,1))))) div 864000000000) >= 1 and ((ddwrt:DateTimeTick(ddwrt:GenDisplayName(string($thisNode/@D_x00e9_lai))) - ddwrt:DateTimeTick(ddwrt:GenDisplayName(string(ddwrt:FormatDate(ddwrt:Today(),4108,1))))) div 864000000000) <= 7

    Sunday, February 05, 2012 12:07 AM
  • Hello ogabriel

    I read ur solution, but as i am new to sharepoint i dont know where to write the above code, as i have similar query.

    My Requirement(Prob)

    I create one new page in which i inserted my list which need filtering

    Whenever, i open that page it must show the records entered since last 10 days, i.e say Today's Date is 9th February 2012 dan it should show records entered on or after 30th January 2012. I tried with the date filter webpart i set Current Date - 10 Days as the default value for my datefilter webpart and filter the list with that, but not able to get the records.

    Please tell me where m doing the wrong or missing something, i will really appreciate for this.....

    Also, as per ur solution which are steps to be taken/consider, Thanks in advance.

    Sorry, for my poor english

    Regards

    Prashant

    Thursday, February 09, 2012 12:31 PM
  • Finally i got the solution,

    I just used the filter option on "Created" and choose "Greater Than or Equal" option and Value as "Current Date"

    than open that page in sharepoint designer when to the CAML Query

    Before:

    <Where>
                        <Geq>
                            <FieldRef Name="Created"/>
                            <Value Type="DateTime">
                                <Today />
                            </Value>
                        </Geq>
                    </Where>
    After:

    <Where>
                        <Geq>
                            <FieldRef Name="Created"/>
                            <Value Type="DateTime">
                                <Today OffsetDays="-10"/>
                            </Value>
                        </Geq>
                    </Where>

    and Bingo!!!!!!!!!! its works

    Regards

    Prashant

    Monday, February 13, 2012 7:23 AM
  • thank you, working version for me

    number(ddwrt:DateTimeTick(ddwrt:GenDisplayName(string($thisNode/@CreatedOn)))) <= number(ddwrt:DateTimeTick(ddwrt:GenDisplayName(string($Today)))) and number(ddwrt:DateTimeTick(ddwrt:GenDisplayName(string($thisNode/@CreatedOn)))) >= number(ddwrt:DateTimeTick(ddwrt:GenDisplayName(string($Today))))-86400000000000


    • Edited by NastL Tuesday, March 06, 2012 8:17 AM
    • Proposed as answer by Rotor1 Tuesday, June 25, 2013 2:20 PM
    Tuesday, March 06, 2012 8:17 AM
  • this works for me also thanks NastL
    • Edited by Rotor1 Tuesday, June 25, 2013 2:21 PM edit
    Tuesday, June 25, 2013 2:20 PM
  • thanks..

    its works perfectly.... :)

    Thursday, December 19, 2013 6:24 AM
  • Prashant, thank you for this solution.  As it turns out a CAML query is the best way to do this because it is faster and less error prone.

    Monday, February 10, 2014 10:04 PM