Comparing dates in DataSheet View using Conditional Formating RRS feed

  • Question

  • [Using SP, SP Designer 2007 ]

    Using SP Designer DataSheetView on Document library , I need to set Conditional formatting on column "Last Revision". I would like to have red backround for documents, where the Last Revision date is more than one year back from the current date.

    I first tried to set the criteria directly in Condition Criteria window, as it was not possible to add any calculation to Current date I clicked for Advanced : there was already following formula: number(translate(substring-before(@Last_x0020_Revision,'T'),'-','')) < number(translate(substring-before($Today,'T'),'-',''))

    I just made following change expecting it to work.

    number(translate(substring-before(@Last_x0020_Revision,'T'),'-','')+365 ) < number(translate(substring-before($Today,'T'),'-',''))

    Unfortunately it set's the background color to red even for document newer than one year . In my example the Last Revision date is 27.10.2010

    I was trying to understand where is the issue and found out that the date is modified and converted by this formula from following 2010-10-27T11:10:37 to 20101027. The same thing is done to current date and those numbers are compared. Thus we will end up with comparing "(20101027 +365)" 20101392 < 20110301 . This is clearly not the way dates could be compared. I was searching for something like to compare date according to their serial nr.  as it's done in excel, but without success. 

    Any idea how to solve this issue? Thank you in advance

    • Edited by Mike Walsh FIN Tuesday, March 1, 2011 2:16 PM Original Title very jumpy replaced by a single sentence
    Tuesday, March 1, 2011 12:28 PM