• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Conditional Formatting with Dates

T

Terry Cameron

New Member
Hello!! I'm having some difficulty with Conditional Formatting using dates. Here's the scene:
In B1, I have the formula =(B1-TODAY())>30, which, when true, fills the cell green when the date is more than 30 days in advance.
In B2, I have the formula =AND((B2-TODAY())>=15,(B2-TODAY())<=30, which, when true, fills the cell yellow when the date entered is between the 15 to 30 day mark.
In B3, I have the formula =(B3-TODAY())<15, which, when true, fills the cell red when this date is less than 15 days in advance.
There is not a date in B4.
This works - it is doing exactly what I require it to do. However, there are cells in the table that will not have dates in them right away and they glow red. This is where my difficulty lies - I need a formula that will make these blank cells remain white until a date is entered. I'm not certain where to even begin or how to construct the formula. After many, many days of trying, I am at a loss. Please help!!
 
Hi, Terry Cameron!
Consider uploading a sample file (including manual examples of desired output if applicable), it'd be very useful for those who read this and might be able to help you. Thank you.
Regards!
 
Terry Cameron said:
Here's the scene:
In B1, I have the formula =(B1-TODAY())>30, which, when true, fills the cell green when the date is more than 30 days in advance.
In B2, I have the formula =AND((B2-TODAY())>=15,(B2-TODAY())<=30, which, when true, fills the cell yellow when the date entered is between the 15 to 30 day mark.
In B3, I have the formula =(B3-TODAY())<15, which, when true, fills the cell red when this date is less than 15 days in advance.
Click to expand...
Hi, Terry Cameron!
If you take a picture of that scene it'd look as if someone has moved it shifting to... where?
There's a problem with your cell references and the cell formulas, you can't have in B1 cell a formula that uses the B1 cell in it. Ok, you can if you use iteration to avoid circular references, but I bet that this isn't the case.
Consider uploading a sample file (including manual examples of desired output if applicable), it'd be very useful for those who read this and might be able to help you. Thank you.
Regards!
 
SirJB7 said:
Hi, Terry Cameron!
...There's a problem with your cell references and the cell formulas, you can't have in B1 cell a formula that uses the B1 cell in it. Ok, you can if you use iteration to avoid circular references, but I bet that this isn't the case...
Click to expand...

...unless the OP is talking about Conditional formatting, which they are. ;) But I'll admit that it is a little confusing that the cell reference keeps changing.
 
Hi Terry ,

Luke's formula :

=AND(ISNUMBER(B4),B4-TODAY()<15)

works correctly ; if you enter this as the CF formula in B4 , and leave B4 blank , it remains uncolored.

Since today is October 22 , if you enter any number ( which is essentially what all dates are ) less than November 6 , it turns red ; any date which is greater than or equal to November 6 will again leave it uncolored.

Using the Format Painter , you can copy this format in B4 to any other cell in the worksheet , and it should work.

Narayan
 
Attached is a sample table of what I'm doing. This type of table will be used to track maintenance dates of equipment. I need it to highlight the cells as follows: green when the date in the cell is 60 days or more in advance, yellow when the date in the cell is between 30 and 60 days in advance, red when the date in the cell is less than 30 days in advance, and finally, for the cell to remain it's natural color should no date be entered at that time. When the cell is in it's red state, it needs to remain red until that date is changed (likely to it's next maintenance date). I have had to alter my formulas from what I previously stated. For the empty cell, I have the formula =AND(ISNUMBER)(A1),A1-TODAY())<0, formatted to no color. For the green cell, =AND((A1-TODAY())>60. For the yellow cell, =AND((A1-TODAY())>=30,(A1-TODAY())<=60). For the red cell, =AND((A1-TODAY())<=30,(A1-TODAY())>0). So far, this seems to work, however, I am concerned about Narayan's statement about red cells changing back to uncolored after a few days. Please advise. Many thanks!!
 

Attachments

Hi Terry ,

Any formula based solution ( and CF uses formulae ) will change especially since the result returned by the TODAY() function changes from day to day.

If you need a cell to retain its present state till it's changed , then you have to use VBA ; an alternative is to not use the TODAY() function in any formulae ; dedicate a cell , say P1 , to having today's date as a static value ( entered by pressing CTRL ; ) so that the next time things change will be when this cell is changed.

Narayan
 
The formula you posted in sample file have a bit of an overlap. If green is >=60, yellow should not be <=60, as that causes an overlap. Assuming the cells that need to stay red are not going to just have the data deleted, your formulas will be:
Green
=AND(ISNUMBER(A1),A1-TODAY()>=60)
Yellow
=AND(ISNUMBER(A1),A1-TODAY()>=30,A1-TODAY()<60)
Red
=AND(ISNUMBER(A1),A1-TODAY()<30)
 
Thank you, thank you, thank you!! :) I believe my table is now working as expected!! Thank you for sharing your expertise with me - I am so very grateful!
I believe this will be the first night in three weeks that I won't dream about formulas!! Thanks again!
 
You must log in or register to reply here.
Back
Top