• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Help on conditional formatting on multiple date criterias

P

Pooja

New Member
Hi,

I have a following scenario for conditional formatting on dates and I am stuck;

I have 3 date columns in my data Original Due Date ; Revised Due Date & End Date and based on this I need following Status of colors

1. RED – end date more or blank look for “revised Due Date” if this is blank look for “original Due Date” more than Today
2. Orange – end date is empty look for “revised Due Date” if blank “original Due Date” is within a week (7 working days) Today + 7
3. Yellow - end date is empty and revised Due Date” OR “original Due Date” is next week (more than 7 and less than 14)
4. Green - end date is equal or less then “revised Due Date” OR “original Due Date
 

Attachments

Last edited:
oldchippy said:
Hi Pooja,

I see you are a new member, I hope you have had chance to read these?
http://chandoo.org/forum/forums/new-users-please-start-here.14/

With regard to your question take a look at this link here, it may help, if not upload a file

http://chandoo.org/wp/2012/05/22/highlight-due-dates-excel/
Click to expand...

Hi,

thanks for your response. I checked the link given by you but I have to check 3 date columns to apply the condition and this is were I am stuck.
I have uploaded :-). let me know if something is not clear.
 

Attachments

Hi, Pooja!
Without having looked at your uploaded file, the CF condition Use Formula (last entry) it's just an expression that should return True or False; if it returns True, then the specified format is applied to the related range. And you can define any number of conditions for the same range, so as to CF with different formats.
Why do I say this? Well, once you learned the technique for writing one condition, you've learned it for all others, no matter what complex they are.
Hope it helps.
Regards!
 
I'll echo and add to SirJB7...

BUT writing a formula from scratch in the by formula option in the CF box can be very difficult. i would suggest writing/tweaking (not twerking) the formula in a new column first. this way you can test and see that it is working properly. then once, you have it perfected you should be able to copy/paste it in the CF 'by formula' optoin.

NOTE: sometimes that darn by formula box will mess with your absolute/relative referencing, so keep a close eye on it after you appy it.
 
Hi Json,

Thanks for the reply, I went with your approch and tried to resolve the query. Based on the formula result I am appling the conditional formatting for on the required pivot table.
 
You must log in or register to reply here.
Back
Top