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
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
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:
- Conditional formatting a date 8 months on
- Samsung mobile reset code 8 digit - Guide
- Psh4x 8 ball pool ios - Download - Sports
- Tekken 8 download - Download - Fighting
- Gta 5 download apk pc windows 8 - Download - Action and adventure
- Bandlab download for pc windows 8 - Download - Musical production
1 response
rizvisa1
Posts
4478
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
766
Jun 22, 2010 at 07:18 PM
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
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
Jun 23, 2010 at 05:56 AM
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
Jun 23, 2010 at 05:58 AM
Jun 23, 2010 at 09:53 AM