I want a formula to display:
- red color when the due date is either today or in the past
- amber color when the due date is in the next 5 days
- green color when the due date is either after 5 days or the date field is empty/blank
I write the following formula but it does not work properly:
IF(CGT_Due_Date__c > TODAY()+5, IMAGE("/img/samples/color_green.gif", "green", 30, 30),
IF( ISBLANK(CGT_Due_Date__c) , IMAGE("/img/samples/color_green.gif", "green", 30, 30),
IF(CGT_Due_Date__c = TODAY() +1 && CGT_Due_Date__c < TODAY() +5, IMAGE("/img/samples/color_yellow.gif", "yellow", 30, 30),
IMAGE("/img/samples/color_red.gif", "red", 30, 30)
) ))