Solved

Excel conditional formatting based on date difference

Posted on 2013-11-16
9
290 Views
Last Modified: 2013-11-18
I don't know how to re-open a question so I am just asking it again.

I don't know why I can't get this to work right, but when the conditional formatting won't copy over correctly to other columns/cells.

I would really appreciate help.  I have to work with other date diffs too but I can't even get these basic ones to work...

What I need on the attached sample worksheet is:

FOR COLUMNS C, D & E                        
1.  IF DATE => 24 MOS FILL RED                        
2.  IF 23 MOS =< DATE =< 24 MOS FILL YELLOW                        
3.  IF CELL IS BLANK CLEAR FILL                        
4.  DEFAULT CELL FILL GREEN
ProviderTrackerSample.xlsx
0
Comment
Question by:fabi2004
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
9 Comments
 
LVL 11

Assisted Solution

by:Gregory Miller
Gregory Miller earned 250 total points
ID: 39653769
The problem as I see it is that you need to change the order of the rules. I believe you want to test for the clear fill first then the red fill and then the yellow fill in that order. The reason is when the cell satisfies a test, it does not test any more.
Copy-of-ProviderTrackerSample.xlsx
0
 
LVL 1

Author Comment

by:fabi2004
ID: 39653802
I had no idea the rules stopped once a test passed!  That's been so frustrating.  Thank you!

But I still don't see that the cell references are correct.  I can't seem to copy the conditional formatting correctly to other cells/rows/columns.  The conditional formatting formula should apply to the date in the current cell, not to the date in a previous cell.

I've tried every combination of the format painter, formula copy, and section selection while adding the formula.  

Any idea?

Thanks
0
 
LVL 11

Expert Comment

by:Gregory Miller
ID: 39653834
So to be clear... Using 11/16/2013 as the point of reference:
All cells with dates will be GREEN (exceptions noted below)
All empty cells will be colorless
All dates between 10/16/2015 and 11/16/2015 will be YELLOW
All dates later than and including 11/17/2015 will be RED

I am trying to read your logic and compare to your formulas and they don't jive. Can you confirm the above or modify it so it is correct.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 50

Expert Comment

by:barry houdini
ID: 39653848
Hello fabi2004,

I think I answered your original query.....

You ended up with multiple rules for different ranges. Technodweeb is right, the order matters, so I deleted all the existing rules and then just added back the original three, in order blank, red then yellow - each one has an "applies to" range of C2:E26 - if you want to add additional columns that are adjacent you can simply change that range.

see attached

regards, barry
ProviderTrackerSample-barry.xlsx
0
 
LVL 50

Accepted Solution

by:
barry houdini earned 250 total points
ID: 39653871
The reason why I suggested rules that need to be applied in a specific order is because that is one way to keep the formulas simple. If you want rules that can be applied in any order then you can adjust the rules.

On the new attachment here your dates should be formatted the same but it's done in a different way.

I removed the green fill applied with normal formatting and then applied these three rules in conditional formatting:

yellow fill

=AND(C2>EDATE(TODAY(),-24),C2<=EDATE(TODAY(),-23))

red fill

=AND(C2<=EDATE(TODAY(),-24),C2<>"")

green fill

=C2>EDATE(TODAY(),-23)

because those rules are all mutually exclusive the order doesn't matter

see attached

regards, barry
ProviderTrackerSample-barry2.xlsx
0
 
LVL 1

Author Comment

by:fabi2004
ID: 39656434
Thank you all for your replies.

Technodweeb - yes, that logic is correct

Barry,  thank you so much for picking back up on the thread.  What I need is to format entire columns.  C,D&E have a 2 year expiration.  Other columns have 1 year or 30 days, etc.

I'm surprised that this has been so difficult for me.  When I started it, I assumed that applying some conditional formatting would be stratight forward.  But the longer I played around with the formulas, the less the formatting worked the way I  needed it to.  I really appreciate your help.

I don't suppose it matters if I use formulas that have to be in order or the new ones you wrote.  As long as I know not to switch them up.  :-)  Thank you for the new workbook.  Let me try to see if I can expand it to include entire columns and add the remaining ones I need with modified formulas.

Thanks again!
0
 
LVL 1

Author Comment

by:fabi2004
ID: 39656493
Do I understand correctly that because these formulas contain a relative reference, they will be applied individually to each cell in the "applies to range"?  In other words, even though the formula references C2, when it tests C15, it will look at the date in C15 instead of the date in C2.  Am I understanding?
0
 
LVL 50

Expert Comment

by:barry houdini
ID: 39657596
Yes, that's correct - if your "applies to range" is, for example E2:G100 then you always use the formula that applies to the top left cell of that range - E2 in my example - and then it will adjust for each cell in exactly the same way as the formula would if you put it in cell E2 on the worksheet and dragged it down (or across)

In Excel 2003 there were many drawbacks to conditional formatting (I thought), but one of the advantages was that you could look at any individual cell and see which formula pplied explicitly to that cell. That was lost since 2007 - you can work out what it will be but it isn't explicitly visible.

In this case then formulas always refer to C2, not $C$2 or some variation, so that they will adjust row by row.

If you want to format the whole column you can just select the whole column first (and then the "applies to" range will automatically be the whole column) and then apply the formula that applies to row 1

regards, barry
0
 
LVL 1

Author Comment

by:fabi2004
ID: 39658179
Thank you so much for all the help and most of all for explaining to me so that I actually learned how this works.

I really appreciate it!
0

Featured Post

Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

740 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question