Sharepoint By Dummies

See Also:

Posts Tagged ‘conditional formatting

Date comparison sometimes uses previous day!

leave a comment »

We have a list which contains a number of date fields, in a dataview connection tot hat list we are using conditional formatting to show/hide alerters depending on if the date has past.  This is on WSS 3.0 and the alerters with their conditional formatting were added using sharepoint designer 2007.  The users reported that sometimes the alterter for overdue (i.e. the date had passed) was appearing a day early.

The XSL inserted by SPD to impklement the conditional formatting is:

<xsl:if test="normalize-space(@STATUS_x0020_FOR_x0020_DASHBOARD) = 'Start Date' and number(translate(substring-before(@Planned_x0020_Start_x0020_Date,'T'),'-','')) > number(translate(substring-before($Today,'T'),'-',''))"><img alt="" src="taxi graphics/Greentick[1].gif" width="16" height="16" /></xsl:if>
<xsl:if test="number(translate(substring-before(@Planned_x0020_Start_x0020_Date,'T'),'-','')) <= number(translate(substring-before($Today,'T'),'-','')) and normalize-space(@STATUS_x0020_FOR_x0020_DASHBOARD) = 'Start Date'"><img alt="" src="taxi graphics/redcross[1].gif" width="16" height="16" /></xsl:if>
<xsl:if test="normalize-space(@STATUS_x0020_FOR_x0020_DASHBOARD) != 'Start Date'"><img alt="" src="taxi graphics/gray tri.jpg" width="10" height="9" /></xsl:if>

We’ve found that in some cases the truncate on the Planned_x0020_Start_x0020_Date field returns the date of the day before the actual date recorded (so for 15-Jun-12 it returns 20120611 rarther than 20120615).

I’ve posted a question about this on EggheadCafe.

Written by stephenboothuk

June 20, 2012 at 2:33 pm

Displaying ‘Unknown’ in a date field when date isn’t known

with one comment

We have a site on Sharepoint 2010 where some of the lists have a date field but the correct value for that field isn’t known for some records.  Because of the nature of the users we do need to display something in that field even if it’s known.  This field has to be a date as it’s used in date comparisons and arithmatic (e.g. filter for when a date is past or within 12 months and apply alerters depending on if it’s passed, in the near future or further in the future) so we can’t insert the string ‘Unknown’.  We do also need, again due to the nature of someof the users, to display a meaningful string in the views, i.e. the string ‘Unknown’.

The workarouind we’ve come up with is to specify that unknown dates will be recorded as 01/01/1900 (this date will never legitmately be recorded in this sytem as an actual date) and set that as the default.  The users who will be entering data are more techie and will be instructed to use that date to mark unknown dates.  In the views that will be accessed by the non-techie users we inserted the string ‘Unknown’ into the table cell just after the data field.  Then we selected the data field and set coinditional formatting to hide it when the value is 01/01/1900, finally we selected the string ‘Unknown’ and set conditional formatting to show it when the value is 01/01/1900.  If the date is a known date it will then display the date, if it’s unknown (so set to 01/01/1900) it displays ‘Unknown’.

Not perfect but it does the job.

Written by stephenboothuk

February 14, 2012 at 2:51 pm

Speeches I Would Have Given

What I would have said at conference

Mark Vale

Tech blog on Microsoft Teams & Stuff

David Hunt, PE... Mechanical Engineer on the loose!

VALUE = Creativity + Diverse Experience + Critical Thinking


A personal blog about having Asperger's

Marc D Anderson's Blog

A Knowledge Management Zealot Speaks Out

Codeless Solutions for SharePoint

Bryon Wyly is a SharePoint Developer dedicated to finding the limits to out-of the-box SharePoint solutions

the political blog of gordon lyew

SharePoint Business Analyst

Just another weblog