Question asked by user @ Excel forum :-
Conditional Formatting based on dates
I have been searching but i cant solve my issue, I want to conditional format a cell that will be either, Green, Yellow or red depending on the date.
This is for drivers licence dates, i want to be alerted when they are 60 days from the expiration etc
To put the “Conditional Formatting” follow below given steps:-
Conditional Format Based on Dates
Find Occurrence of Text in a Column
How to Highlight a row on the basis of Cell
Compare 2 Columns and Return Fill Red if is different
How to check the row and then highlight the first cell of the row
Highlight Cells Tomorrow Excluding Weekend
Conditional Formatting to Mark Dates on a Calendar
How to apply Conditional Formatting in a Cell before a Particular Character
Highlight the Top 10 Sales through Conditional Formatting
Conditional Formatting for Pivot Tables
Conditional Format Between First and Last Non-Blank Cells
The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.
Hi,
i am trying to figure out how to do the following:
Column G (01/14/2019 Column K 01/15/2019
I would like column K to highlight if column G's date is on or after the date on column K
so for the example above, column K should highlight because the date is after column G
I hope it makes sense.
thanks for your help.
the mentioned formula does not work because the result for:
C2"" returns a #"NAME" error
this formula works for me
=AND(NOT(ISBLANK(C2)),C2< TODAY()-60)
What about...
Home/ Styles/ Conditional Formatting/ Highlight Cells Rules/ Greater Than...
Format cells that are GREATER THAN: ==> =TODAY()+60
with ==> Custom Format... (select desired green format)
Home/ Styles/ Conditional Formatting/ Highlight Cells Rules/ Between...
Format cells that are BETWEEN: ==> =TODAY()+30
and ==> =TODAY()+60
with ==> Custom Format... (select desired yellow format)
Home/ Styles/ Conditional Formatting/ Highlight Cells Rules/ Between...
Format cells that are BETWEEN: ==> 1/1/1910
and ==> =TODAY()+30
with ==> Custom Format... (select desired red format)
Don't forget the "=" before "TODAY()".
I hope this is helpful.
I forgot to state at the beginning that, of course, you want to highlight the Expiration Date column.
I believe Krish to be correct about there being errors in your formula. If C2 is less than today minus 30 then any date over a month in the past would be yellow.
Likewise if C2 is greater than today minus 60 then any date after 60 days ago will be red and also into the future including today and into the future
Hello Daniel,
Yes, we have found an error and the same we had already revised on the page.
Thanks for bringing this in our knowledge.
Thanks,
Team Excel Tip & Excel Forum
Hi Admin,
I have reviewed the formula and still believe it to be incorrect, based on the idea of the question.
In the formula you are using "Today - No.", which means you are looking into the past. Whereas I believe the question is asking us to look into the future (ie 60 days from today), therefore we should be using "Today + No." or "C2 - No.".
I too am having difficulty getting this formula to work despite applying today's dates and adjusting the others as follows:
Date Days Exceed Expiration Date
16/06/2015 62 17/08/2015
16/06/2015 -122 14/02/2015
16/06/2015 -30 17/05/2015
Hello,
There were some prob with range in the conditional formatting due to which the formula was not working. We have updated the sheet as well as the order.
Team Excel Forum & Excel Tip
Please explain the formula. Why And is used. Why not IF?
Hi Latha,
Thanks for taking time in reading our post.
We haven't used IF because the result easily can be done with "AND" function. If we use "IF" condition, then also we have to use AND along with IF because there are two conditions which needs to be TRUE. Once both condition will be TRUE, then only IF will show the result else nothing.
But if you have any alternative where we can skip AND function and could be done without multiple condition, will highly appreciated.
Thanks,
Team Excel Forum & Excel Tip
Surely this is all wrong, shouldn't it be:
=AND(C2””, C2>TODAY()+60)
=AND(C2””, C2<=TODAY()+60)
=AND(C2””, C2<=TODAY()+30)
And set last one to highest priority.
Hi Krish,
Thanks for taking time to look at the tricks.
It would be nice if you can review your formula and can tell us what are you trying to say here. Also, it would be great if you read the original question posted carefully. Also, the formula which you are trying to use, will never return to TRUE as both condition will never meet. Can you check once and let us know.
The Original condition is:
I have been searching but i cant solve my issue, I want to conditional format a cell that will be either, Green, Yellow or red depending on the date.
If the date I enter in Cell C2 is more than 60 days from today the cell should be green.
If the date is less than 60 days from today the cell should be yellow.
If the cell is less than 30 days from today the cell should be red
I tried the formula here and it doesn't work for me.
The way I read the question is if, assume today is 16/06/2015. Then tomorrow is red, 16/07/2015 is yellow, 16/08/2015 is green. Is this not the idea?
In your example, if you assume your "today" was 15/04/2015, then you had 1 date in the future and 2 are in the past? I'm a little confused as to why you would do this.
My formulas work for what I'm trying to achieve from the original question, but yours do not. Obviously, I forgot to add in "" in each of the formulas.
"not equal to arrows"*
Hi Krish,
The beauty of excel is to get the result via multiple ways. Here, AND function is taking care of value should not be equal to Blank.
If you have another way to get this output then I am looking forward to it.
I just checked your formula & found there is error. Please check.
Regards,
Ashish
As answered above I am answering the question in a different way, as I feel the dates used in the example aren't relevant to what the question originally asked.
Indeed I did mess up with the formulas:
=AND(C2””, C2>TODAY()+60)
=AND(C2””, C2<=TODAY()+60)
=AND(C2””, C2<=TODAY()+30)
Is what I should've posted.
Hmm whenever I type in the "not equal to" arrows they seem to disappear from my comments. Apologies as the formula is wrong again, because of this.
Hi Krish,
we have updated the sheet, please let us know if it works fine now at your end.
Thanks,
Team Excel Forum & Excel Tip