none
How do expiration dates to show 30 days prior in Red in excel

    Question

  • Dear Office 2007/Excel experts,

    This may not be the question compatible with the forums topic, but unfortunately I couldn't find a specific forum for Microsoft Office 2007. Maybe someone completed such task before or knows how to accomplish it.


    I am using Excel 2007.
    Specific Example:

    In one cell I input todays date - the date when the document is issued  in the second cell I input the date when the document will expire.
    My goal is to make the cell with date of expiration turn RED COLOR in 30 days before that date takes place in other words I  should recognise expiration of the document by different color. So that I will be informed that the I should get on with the document renewal in one month before the document expiration.

    There should be an easy formula/method.

    Please provide further help.
    This is very urgent.
    • Moved by Jennifer ZhanModerator Friday, September 17, 2010 1:37 AM (From:Office 2007 Planning and Deployment)
    Wednesday, September 15, 2010 11:53 AM

Answers

  • Hi,

    With todays date in cell A1 and the expiry date in cell B1, select cell B1 click on Conditonal Formating, select 'New Rule', then select the last option 'Use a formula to determine which cells to format', then where prompted type this formula =B1<=TODAY()+30, click on 'Format', select the Fill tab, click on Red, then click 'OK' when prompted.  Now when the date in B1 is 30days or less than the date in A1 it will be coloured red!  Simple eh!

     

    Cheers

    Friday, September 17, 2010 4:45 PM

All replies

  • Hi Madpooh,

     

    You can use Conditional Formatting in the second cell, put your cursor on second cell, choose Conditional Formatting on Style panel, choose the styling format / display, and put your rule.

    For Conditional Formatting you can click http://www.homeandlearn.co.uk/excel2007/excel2007s6p2.html

    Thursday, September 16, 2010 2:28 AM
  • Hi,

    With todays date in cell A1 and the expiry date in cell B1, select cell B1 click on Conditonal Formating, select 'New Rule', then select the last option 'Use a formula to determine which cells to format', then where prompted type this formula =B1<=TODAY()+30, click on 'Format', select the Fill tab, click on Red, then click 'OK' when prompted.  Now when the date in B1 is 30days or less than the date in A1 it will be coloured red!  Simple eh!

     

    Cheers

    Friday, September 17, 2010 4:45 PM