Solved

Excel conditional formatting based on date difference

Posted on 2013-11-16
9
249 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
  • 4
  • 3
  • 2
9 Comments
 
LVL 11

Assisted Solution

by:Technodweeb
Technodweeb earned 250 total points
Comment Utility
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
Comment Utility
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:Technodweeb
Comment Utility
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
 
LVL 50

Expert Comment

by:barry houdini
Comment Utility
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 50

Accepted Solution

by:
barry houdini earned 250 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Sparklines have been introduced with Excel 2010 and are a useful tool for creating small in-cell charts, used for example in dashboards. Excel 2010 offers three different types of Sparklines: Line, Column and Win/Loss. What it does not offer is a…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

771 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

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now