Conditional Formatting 6 months left

ashani

Board Regular
Joined
Mar 14, 2020
Messages
202
Office Version
  1. 365
Platform
  1. Windows
Hi everyone,

Please can someone guide me. I have set the formula in Cell B1 that if there is a date in A1 then it will automatically calculate 3 years after. I'm looking to put conditional formatting that B1 turn red if there is a 6 months left for 3 years.

Please can someone help me.

thank you.
 

Some videos you may like

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

afonsomira

Board Regular
Joined
Aug 11, 2020
Messages
62
Office Version
  1. 365
Platform
  1. Windows
Hello,

See if this help you.

  1. Select the Cell B1
  2. Go to Tab Home
  3. Click on Conditional Formatting
  4. Click on New Rule...
  5. Choose "Use a formula to determine which cells to format"
  6. Past the next formula "=DATEDIF(TODAY(),B1,"m")<=6"
  7. Click format and format your way
  8. Then click Ok on the 2 windows
 

ashani

Board Regular
Joined
Mar 14, 2020
Messages
202
Office Version
  1. 365
Platform
  1. Windows
Hello,

See if this help you.

  1. Select the Cell B1
  2. Go to Tab Home
  3. Click on Conditional Formatting
  4. Click on New Rule...
  5. Choose "Use a formula to determine which cells to format"
  6. Past the next formula "=DATEDIF(TODAY(),B1,"m")<=6"
  7. Click format and format your way
  8. Then click Ok on the 2 windows
hi

Thanks for your prompt response

sorry its not working - i want B1 to turn amber if 6 months left before the expiry for example today is 17/12/2020 so B1 should go amber from 17/06/2020.

thanks ocne again
 

afonsomira

Board Regular
Joined
Aug 11, 2020
Messages
62
Office Version
  1. 365
Platform
  1. Windows
Check this formula.
=IFERROR(DATEDIF(B1,TODAY(),"m"),"") <= 6
 

ashani

Board Regular
Joined
Mar 14, 2020
Messages
202
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

thanks once again
this works but than it stopped other conditional formatting which turns Red if it's reached to expiry dates currently I have formula =A1<today().
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,717
Office Version
  1. 365
Platform
  1. Windows
this works but than it stopped other conditional formatting which turns Red if it's reached to expiry dates currently I have formula =A1<today().
If you have multiple conditional formatting rules applied to the same cell, you must make some decisions.

It is possible to have a cell formatted with multiple conditional formatting rules on the same cell, as long as they do not interfere with each other.
For example, you cannot have one that says turn the text red, and the other that says turn the text blue, and expect both to work (since they are both coloring the text).
However, you could have one that says turn the text red, and the other that says make the text bold and italic, and apply both.

If you have multiple CF rules trying to affect the same formatting (i.e. shading of the cell), then you must order them and determine which rule takes precedence over the other.

If you go to your cell, and select Conditional Formatting, and pick "Manage Rules", you will see all the rules applied to that cell.
Note that you can change the order by moving rules up and down.
And also notice the "Stop if True" checkbox on the far right of every rule. If you have that checked, if that condition is met, it will not check anything underneath it.
So if you want to be able to apply multiple formats to a single cell, you will probably want to make sure that all those boxes are NOT checked.

Additionally, sometimes you might need to modify your rules to make sure that the different rules are exclusive (i.e. exclude the conditions of the other).
You can usually do this with AND and/or OR functions.
 
Solution

ashani

Board Regular
Joined
Mar 14, 2020
Messages
202
Office Version
  1. 365
Platform
  1. Windows
thank you Joe for clarifying and guidance.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,717
Office Version
  1. 365
Platform
  1. Windows
You ae welcome.
 

Watch MrExcel Video

Forum statistics

Threads
1,128,017
Messages
5,628,153
Members
416,296
Latest member
smartua

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top