I'm looking for a conditional formatting formula to keep track of follow-on
contract due dates. I've found many helpful formulas for calculating due
dates from a starting date...but, not working backwards from an end date.
Any ideas?

Some contracts are on a fiscal year schedule (30 Sep) and some are
off-cycle (any date.) How do I set up a formula that will color a cell red
one month before contract expiration and turn the cell yellow 180 days before
contract expiration. The formula must take into account that the cell
includes the expiration date.

Thanks in advance!

Hi,

you can use conditional formatting on cells with expiration date, by using
the formula (for 30 days assuming that expiration date is in cell a2
comparing to current date):
=SUM(A2-NOW())<31

You can add another condition to conditional formatting for 180 days.

Regards,
Nika Lampe

"eppersbl" je napisal:

I'm looking for a conditional formatting formula to keep track of follow-on
contract due dates. I've found many helpful formulas for calculating due
dates from a starting date...but, not working backwards from an end date.
Any ideas?

Some contracts are on a fiscal year schedule (30 Sep) and some are
off-cycle (any date.) How do I set up a formula that will color a cell red
one month before contract expiration and turn the cell yellow 180 days before
contract expiration. The formula must take into account that the cell
includes the expiration date.

Thanks in advance!

Thanks! I definitely think this might work!

"Nika Lampe" wrote:

Hi,

you can use conditional formatting on cells with expiration date, by using
the formula (for 30 days assuming that expiration date is in cell a2
comparing to current date):
=SUM(A2-NOW())<31

You can add another condition to conditional formatting for 180 days.

Regards,
Nika Lampe

"eppersbl" je napisal:

I'm looking for a conditional formatting formula to keep track of follow-on
contract due dates. I've found many helpful formulas for calculating due
dates from a starting date...but, not working backwards from an end date.
Any ideas?

Some contracts are on a fiscal year schedule (30 Sep) and some are
off-cycle (any date.) How do I set up a formula that will color a cell red
one month before contract expiration and turn the cell yellow 180 days before
contract expiration. The formula must take into account that the cell
includes the expiration date.

Thanks in advance!