Expiration Date Formula

[waldronl]

I believe this has been addressed before, but I have a spreadsheet where I keep track of insurance policies, licences, etc. I need to have the date highlighted in yellow 30 days prior to expiry and in red 7 days prior to expiry. I can't seem to figure out the formula.

[Pete_UK]

You need to use Conditional Formatting for this. Select the cell(s) that you want this to apply to, and in the CF dialogue box click on New Rule then choose the option Use a Formula ... Then you can enter this formula:

=AND(cell>=TODAY(),cell<=TODAY()+7)

where cell is the first cell in the highlighted range that contains the expiry date, then click the Format button, Fill tab, then choose red and OK your way out. Do it again for the second condition, but use this formula:

=AND(cell>TODAY()+7,cell<=TODAY()+30)

and use a yellow background.

Hope this helps.

Pete

[Andrew1983]

hello!

You must use conditional formating. Formulas you need to add follows:
1) "a-today()<=7" - next you need to add chooose red colour
2) "a-today()<=30" - next you need to add chooose yellow colour

where
a- date of expiry

For dates between today()-30 and today()-8 the range is yellow and for dates between today()-7 and today() the range is red. if you don't want to use conditional formating you may use macro of VBA.

Sorry for my english- i'm not from UK....and i'm just practise my english skills

Andrew