Highlight row in SharePoint list using Calculated Field

Using calculated column to write html has been well known since SharePoint 2007. I always like to do this because it does not require complex customization on xslt or even SharePoint designer. Today I check out this blog which has more good examples: http://sharepointwijzer.nl/sharepoint-blog/tech/icc-html-calculated-column-sharepoint-view/#ICCadvanced

I would like to share a simple solution to highlight rows in SharePoint list using calculated field.


Calculated formula:

=IF([YesNo],"<img src='/_layouts/images/blank.gif' onload='javascript:{this.parentNode.parentNode.parentNode.style.backgroundColor=""Yellow"";}'>","")

Simple. Hope this save the time many out there seeking for quick solution.

Date Time duration using Calculated Field column

Calculated Field column can be use effectively to get the differences or duration between two date time. There is a list of useful formula in MSDN: Calculated Field Formulas. But some of the formula is actually quite buggy and not providing the accurate result. Another difficult part is to get the result in the preferred format.

Just to share some of the formula I modified recently to get accurate calculation and in my desired format.

Format: d hh mm (Day Hour Minutes)

=DATEDIF([Start Time],[End Time]-(MOD([Start Time],1)>MOD([End Time],1)),”d”)&” Day(s) “&TEXT(MOD([End Time]-[Start Time],1),”hh”” Hour(s) “”mm”” Min(s)”””)

Format: hh mm (Hour and Minutes)

=(DATEDIF([Start Time],[End Time],”d”)*24+HOUR([End Time]-[Start Time]))&” Hours(s) “&MINUTE([End Time]-[Start Time])&” Min(s)”