conditional formatting dates

[coltheplumb]

Hi All, ok I need cells to highlight when they are 90 days from expiring and continue to be highlighted until 1 day after they have expired, so say the date in the cell is the 1/5/18 and so when we actually get to the 31/1/18 I want it to turn yellow and then when we actually get to the 1/2/18 it will go red. All help appreciated Col X

 

[MARK858]

What cells are the dates in? is there an expiry date in a cell (if so what cell?) or a start date we can calculate the 90 days from (if so what cell?).

Explain in more detail please.

 

[coltheplumb]

Hi mark858, the dates can be in a few various cells, but at the moment I am putting the date in B3. the dates are for when a qualification runs out and has to be re taken and so I am after something that shows when I have 90 days to re take the exam

 

[MARK858]

Not sure if I understanding you correctly but put 2 conditions on cell B3

=TODAY()=$B3-90

With fill colour yellow.

=TODAY()>$B3-90

With fill colour red.

 

[manojrf]

Hi coltheplumb,

Sheet1

	A	B	C

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"></colgroup><tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]
[TD="align: right"]01-05-18[/TD]
[TD="bgcolor: #ffff00, align: right"]31-01-18[/TD]
[TD="bgcolor: #ff0000, align: right"]01-02-18[/TD]

</tbody>

Conditional formatting

Cell Nr.: / Condition Format B1 1. / Formula is =$B$1=$A$1-90 <tbody> [TD="bgcolor: #ffff00"]Abc

<tbody>

C1	1. / Formula is =$C$1=$A$1-89

[TD="bgcolor: #ff0000"]Abc[/TD]

</tbody>

[/TD]

</tbody>

Excel tables to the web >> Excel Jeanie HTML 4



I think this is what you need.



Excel tables to the web >> Excel Jeanie HTML 4

 

[coltheplumb]

ok so I was hoping that I wouldn't have to use cell references as the CF would be in the same cell as the date?

 

[MARK858]

Colin, you put the conditional formatting I posted both on the same Cell (in this case B3) then use the format painter to paint to any other cell (at the moment it is set for column B, if you are using it on other columns remove the $).

To test put the date 21/4/18 in B3 as that is 90 days from today.

See the workbook in the link below


https://app.box.com/s/iez5cfas5blcpn7qil8gkc47x76catss

 

[coltheplumb]

Hi again, no that does not work how I want it to, once the cell goes yellow when the date in it becomes less than 90 days from todays date I need the cell to remain yellow when it is 89 days less, 88 days less etc etc right up until the date ends up being yesterdays date (in real terms) when the cell will need to remain red for ever or until the date is changed again to say 1 year from now. I hope that makes sense.

 

[MARK858]

Try

=AND(TODAY()>B3-90,TODAY()<=B3)

for the yellow, make sure stop is true is ticked.

Don't understand your red requirement.