• 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.

Why Do I Struggle with Conditional Formatting?

T

test

New Member
Hi,

1st, I appreciate the help. I continuously have difficulties with conditional formatting. And, match & index, but that is for another post. Today, my issue is with conditional formatting. Here are the 2 scenarios:
1) a 3-column Excel 2010 spreadsheet: A) Name and B) Expiration Date and C) Notes; Names can have same or different expiration date; Expiration date could be in the past IF the person's action is in progress, which will be annotated in the notes column; desire is to have Expiration date column color coded based on comparison of expiration date to today's date. If there is no expiration date then the cells should not have any color formatting, but if a new name is entered with an expiration date the cells should already be conditional formatted.
Due within 6 Months = orange
Due within 1 Year = yellow
Due Over 1 Year = green
Past Due = red
example: today is June 26, 2014 so an expiration date of August 3, 2014 would be orange; an expiration date of Mar 18, 2015 would be yellow; an expiration date of October 20, 2015 would be green; expiration date = June 10, 2014 = red

I have tried these formulas:
=TODAY()<=EDATE($B2,-6) (orange color)
=TODAY()<=EDATE($B2,-12) (yellow color)
=TODAY()<=EDATE($B2,12) (green color)


2) a 32-column Excel 2010 spreadsheet: A) Name and the next 31 columns correspond to date of month - 1 - 31; attendance spreadsheet; 10 types of attendance/absence status: 1) unexcused / U = red color; medical / M = blue; vacation / V = blue; late / L = orange; non-work day / NWD = green; at work / AW = green; approved absence / AA = blue; sick / S = yellow; business trip / BT = purple; excused / E= green. If there is no attendance / absence code entered then the cells should not have any color formatting, but if a new name is entered, or an attendance / absence code is entered, the cells should already be conditional formatted. I have not tried creating formulas for this scenario yet.

Thanks for the help.
 
Hi test,
Just to make sure we're on the same page, could you upload a sample workbook with some of your data, highlighting the colors/rules you want to setup?
 
Hi ,

I have done the first one on expiration dates.

The first thing to remember about CF is that whatever formula you put in , should refer to the top left hand corner of the range to which you are applying your CF.

In your case , you have selected the Applies To range as $B:$B ; once you do this , Excel will use the formula as if it refers to the top left corner of the range , which will be cell B1. Your formula uses B2 ; Excel will interpret this to mean that every cell in the range should be formatted according to the rule , using the cell below the referred to cell in the formula i.e. cell B1 will be formatted based on the formula as applied to cell B2 , cell B2 will be formatted based on the formula as applied to cell B3 , and so on.

I have uploaded the wrongly formulated file so that you can compare the two.

Narayan
 

Attachments

  • Chandoo Example Wrong.xlsx
    11.3 KB · Views: 2
  • Chandoo Example.xlsx
    11.3 KB · Views: 5
Any progress on my scenario #2? I believe I can combine the status's that use the same color but I am unsure of how to even start the formula to use for the conditional formatting. Can one of you give me a hint so I can try and then get back to you? Thanks.
 
Hi ,

Can you first confirm whether your first question has been addressed satisfactorily or not ?

Secondly , for your second problem , you have 10 types of statuses ; some of these do not seem to be mutually exclusive ; for instance what is the difference between Medical and Sick ? What is the difference between Approved Absence and Excused ?

In any case , can you rank the statuses in order of priority , or can you confirm that each cell will have only one status in it ?

Narayan
 
Good Morning,

Yes, the 1st scenario is working great. I can confirm that it is what I needed. THANKS!

Medical is extended absence like for surgeries, accidents, etc., something that is going to have you out of work for medical reasons for more than 3 consecutive work (M-F) days.
Sick is when you wake up and cannot come in to work.
Approved absence is when a employee is gone from work but does not have the vacation time to charge
Excused is when an employee allowed to miss work without being charged vacation and is also not considered absent from work.

A person could have more than one status in a cell. As an example, they could be late but then be on vacation in the afternoon. Or, they could be late but At Work the rest of the day. Other than that, there is no real priority.

1) unexcused / U = red color;
2) medical / M = blue;
3) vacation / V = blue;
4) late / L = orange;
5) non-work day / NWD = green;
6) at work / AW = green;
7) approved absence / AA = blue;
8) sick / S = yellow;
9) business trip / BT = purple;
10) excused / E= green.
 
with a slash

L / AW

AW / AA if a person was at work in the morning and then gone in the afternoon

AW / BT if a person was at work in the morning and then leaves for a business trip

S / AW if a person was sick in the morning but then eventually rose from their deathbed and made it in to the office to infect others with their zombie disease. LOL
 
Hi ,

I assume that there will not be more than 2 statuses in a cell ; is that correct ?

Secondly , if there are multiple statuses in a cell , the CF rule which is satisfied first will decide the color of the cell ; is this OK ?

Thus if a cell contains L / AW , then depending on which rule is entered first , the cell can be colored either orange or green. Is this OK ?

Narayan
 
You must log in or register to reply here.
Back
Top