Excel conditional formatting based on date difference

Posted on 2013-11-16
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                        
Question by:fabi2004
  • 4
  • 3
  • 2
LVL 11

Assisted Solution

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

Author Comment

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?

LVL 11

Expert Comment

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.
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

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
LVL 50

Accepted Solution

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


red fill


green fill


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

see attached

regards, barry

Author Comment

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!

Author Comment

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?
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

Author Comment

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!

Featured Post

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

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

How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

831 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