[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now


Excel conditional formatting based on date difference

Posted on 2013-11-16
Medium Priority
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
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
LVL 12

Assisted Solution

by:Gregory Miller
Gregory Miller earned 1000 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 12

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.

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

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

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!

Question has a verified solution.

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

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 article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
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 the scrolling table in Microsoft Excel using the INDEX function.

650 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