› Forums › vLooup for SharePoint › Conditional Format of column??
- This topic has 6 replies, 3 voices, and was last updated 8 years, 1 month ago by
Alexander Bautz.
-
AuthorPosts
-
-
December 12, 2015 at 03:02 #9571
jstadick
ParticipantI have a date field in a vlookup. If this date is < today, I would like the format the font color (CSS) to red. Any ideas?
-
December 12, 2015 at 09:34 #9573
Alexander Bautz
KeymasterHi,
Use a custom function like this.1. Add “Special configurations” to the field in vLookup setup – see the attachment “ConditionalFormattingDate_1”.
2. Add function to the Justom JS:function conditionalFormattingDate(a,b){ var today = new Date(), cDate = b.get_item("Date"); // Date is my date columns fieldinternalname if(cDate < today){ a = "<span style='color:red;'>"+a+"</span>"; } return a; }
The end result should look like the attached image “ConditionalFormattingDate_2”.
-
December 12, 2015 at 15:49 #9578
jstadick
ParticipantAlexander, as always, that works great. Now, I was already using a special configuration to format the date as so {“dateFormat”:”MM/dd/yy HH:mm”}. How do I combine the two or just return the date with this format?
Thanks again!
John
-
December 13, 2015 at 20:43 #9582
Alexander Bautz
KeymasterHi,
Thank you for the beer!Unfortunately you loose the “dateFormat” when using the above code. As the original code for formatting are not directly accessible you will have to change the code like this to be able to return the formatted value.
function conditionalFormattingDate(a,b){ var today = new Date(), cDate = b.get_item("Date"); // Date is my date columns fieldinternalname a = spjs_formatDate(cDate,"MM/dd/yy hh:mm"); // Supply the format here if(cDate < today){ a = "<span style='color:red;'>"+a+"</span>"; } return a; } function spjs_formatDate(a,f){ try{ f = f.replace("MM",(a.getMonth()+1) < 10 ? "0"+(a.getMonth()+1) : (a.getMonth()+1)); f = f.replace("dd",a.getDate() < 10 ? "0"+a.getDate() : a.getDate()); f = f.replace("yyyy",a.getFullYear()); f = f.replace("yy",a.getFullYear().toString().substring(2)); f = f.replace("hh",a.getHours() < 10 ? "0"+a.getHours() : a.getHours()); f = f.replace("mm",a.getMinutes() < 10 ? "0"+a.getMinutes() : a.getMinutes()); f = f.replace("ss",a.getSeconds() < 10 ? "0"+a.getSeconds() : a.getSeconds()); return f; }catch(ignore){ return a; } }
Let me know how this works out.
Alexander -
December 14, 2015 at 16:54 #9592
jstadick
ParticipantWorked like a charm!
Thanks again,
John
-
June 23, 2016 at 13:51 #12092
Chris Diltz
ParticipantAlexander, how would this be different for a text/choice column (e.g., health)?
-
June 29, 2016 at 05:42 #12153
Alexander Bautz
KeymasterYou would have to make a switch statement like this example (change function name in vLookup config to match the one below):
function conditionalFormattingChoice(a,b){ switch(a){ case "green": a = "<span style='color:green;'>"+a+"</span>"; break; case "yellow": a = "<span style='color:yellow;'>"+a+"</span>"; break; case "red": a = "<span style='color:red;'>"+a+"</span>"; break; } return a; }
Change the cases to match your choice column values.
Hope this helps,
Alexander
-
-
AuthorPosts
- You must be logged in to reply to this topic.