# Conditional Formatting 6 months left

#### ashani

##### Board Regular
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.

thank you.

### 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
Hello,

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
Hello,

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

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
Check this formula.
`=IFERROR(DATEDIF(B1,TODAY(),"m"),"") <= 6`

#### ashani

##### Board Regular

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

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.

#### ashani

##### Board Regular
thank you Joe for clarifying and guidance.

You ae welcome.

Replies
4
Views
158
Replies
3
Views
61
Replies
6
Views
103
Replies
5
Views
148
Replies
5
Views
329

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.

### Which adblocker are you using?

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

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