Conditional Format of column??

[jstadick]

I 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?

[Alexander Bautz]

Hi,
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”.

Attachments:

1. 

   ConditionalFormattingDate_2.png
   

2. 

   ConditionalFormattingDate_1.png
   

[jstadick]

Alexander, 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

[Alexander Bautz]

Hi,
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

[jstadick]

Worked like a charm!

Thanks again,

John

[Chris Diltz]

Alexander, how would this be different for a text/choice column (e.g., health)?

[Alexander Bautz]

You 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

[Author]

Posts