Previous Topic Next Topic

Conditional formatting a date 8 months on

Closed
ClaireEmerald - Jun 22, 2010 at 10:04 AM
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 - Jun 23, 2010 at 09:53 AM
Hello,

I am trying to set up a conditional format to change the colour of a cell 8 months from the date in another cell. e.g. when we accept a maintenance contract i fill in the date it commenced and in another cell i have formatted it to show a renewal reminder 8 months on, as this date becomes closer I would like the cell to change colour i.e. yellow 3 months, green 2 months, red 1 month until we need to renew the contract.

I am using excel 07.

I hope this makes sense


Related:

1 response

Answer 1 / 1
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Jun 22, 2010 at 07:18 PM
One month, two month and three month from today's date?

If that is the case

Click on Conditional formatting and add these formula

=AND(B2<>"", DATE(YEAR(B2),MONTH(B2) -1, DAY(B2))<=TODAY(), B2>TODAY())
and choose green color

=AND(B2<>"", DATE(YEAR(B2),MONTH(B2) -2, DAY(B2))<=TODAY(), B2>TODAY())
and choose orange color

=AND(B2<>"", DATE(YEAR(B2),MONTH(B2) -3, DAY(B2))<=TODAY(), B2>TODAY())
and choose red color
0
ClaireEmerald
Jun 23, 2010 at 05:56 AM
Hi

Thank you for your help.

It is actually 1, 2, 3 months from a date in history i.e. say we sold a maintenance contract in July 2009 i fill that date in cell C3 we would need to renuew that contract 12 months later so in cell C4 I have a formula set to automatically populate the cell with the date 12 months on but as the renewal date becomes within 3-1 months of this renewal date the cell's colour changes

Hope that explains it abit better
0
ClaireEmerald
Jun 23, 2010 at 05:58 AM
Sorry C3 is contract start date and D4 is renewal date
0
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Jun 23, 2010 at 09:53 AM
Sorry i am confused. Could you please upload a sample file with sample data, CONDITIONAL FORMAT formula etc on some shared site like https://authentification.site , http://wikisend.com/ ,https://accounts.google.com/ServiceLogin?passive=1209600&continue=https://docs.google.com/&followup=https://docs.google.com/&emr=1 http://www.editgrid.com etc and post back here the link to allow better understanding of how it is now and how you foresee. Based on the sample book, could you re-explain your problem too
0

Similar discussions

Highlight cell if another cell contains specific text
Abbi -
vcoolio -

1 response
Samsung E1200Y Phone Code Reset
dineshkaushik -
Neerajmaurya -

7 responses
How to use conditional format based on text?
Leanne99 -
Mooseman -

26 responses
SanDisk cruzer blade 16GB not recognising...
josco -
bunny -

11 responses