• 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 by date

A

Anthony Pinnegar

Member
I would like some columns in my spreadsheet (J6 and K6) to change colour 45 days after a date that is recorded in cell I6..
Within the conditional formatting menu there are a number of possibilities, but can anyone point me in the right direction, or suggest a suitable, simple, formula to use ?

Thanks in advance.

Anthony
 
Anthony Pinnegar said:
I would like some columns in my spreadsheet (J6 and K6) to change colour 45 days after a date that is recorded in cell I6..
Within the conditional formatting menu there are a number of possibilities, but can anyone point me in the right direction, or suggest a suitable, simple, formula to use ?

Thanks in advance.

Anthony
Click to expand...
hey Anthony,

can u pls upload a sample file to do that???

regards,
Jaya
 
Hi Jaya, in the attached file G6 is conditionally formatted to go Green when someone is allocated an Audit.
G7 to G13 will stay clear until more than 45 days has elapsed. At this point, if the Audit has not been completed (by manual entry) the cells should go Red to show that it is still outstanding.
 

Attachments

Hi Anthony you need to select cells you want to conditionally format

then go to conditional format, new rule, use formula

and then type in =today-cell reference (this is cell with date from which you start counting) > 45

be carefull about absolute and relative references

then set desired and beloved red colour

I did something like that in attached file
 

Attachments

Thanks tomas. How do I incorporate the fact that the start date on each line is different ?
Do I have to manually enter all the 'cell references' within the Conditional formatting formula for each line - eg/ =today<>,I6+45, then I7 on the next line, I8 on the next, and so on.
There are over 66,000 lines on each spreadsheet.
 
Hi

If on G6 you set the conditional formatting as:
=($I6-$A$2)>=45
Note 1: A2 is the cell where you have formula =today()

and drag down, the condition will be applied the way you desire.
 
Thanks again Tomas.
How do I change the formula so that the cell only goes Red if there is a date in I6. If there isn't a date in column I the cells should be clear.
 
Last edited:
My Formula conditionally formats cells G7:G13 and does it in case cells I7:I13 are not empty and difference between today's date and dates in cells I7:I13 is less then 45 days.

This is how I understood your requirement .

If you want , manually format what you need so I can better understand it
 
I have got it working thanks tomas.

The only reason it wouldn't work was because I had an embedded formula in cells I7 to I13 that pulls data through from a summary sheet, and as a result the formula in the conditional formatting rule thought there was something entered in the cell.

I have linked the formula to the summary sheet where there is no data, or formula, entered :)
 
You must log in or register to reply here.
Back
Top