none
Excel Expiration Date

    Question

  • Using conditional formating what do I enter to make dates in column E7 turn red when they expire with in 30 days of the date listed in the cells of that column?
    Wednesday, March 16, 2011 11:10 PM

Answers

  • For anyone following this the formula was correct but the problem was caused by the column containing blanks that were formatting due to the conditional test.

    Revised the test formula to

    =and(now()>e7-30,len(e7)>0)

    Now the blank cells are ignored.


    G North MMI
    • Marked as answer by Sally Tang Friday, March 25, 2011 8:08 AM
    Tuesday, March 22, 2011 7:17 PM

All replies

  • Hi

    I'm assuming E7 is the top of your column of dates.  If so you need to create a rule using the formula

     =E7<Now()+30

    and set the format to red (text or fill).

    If you highlight all the cells in the column before defining the format you should be OK.

    Hope this helps


    G North MMI
    Thursday, March 17, 2011 6:33 PM
  • I'm missing something here. When I do that the entire column turns red. Each cell conatins an expiration date for an insurance policy for example 5/1/2011 and I want it to turn red 30 as of 4/1/11. What am I missing?
    Lisa Marrone
    Thursday, March 17, 2011 8:59 PM
  • OK

    =Now()>E7-30

    E7 is the top of the column


    G North MMI
    Thursday, March 17, 2011 11:06 PM
  • Boy do I feel stupid, but it is not working.

    I highlight E7 to E176 as this is the area that contains my dates.

    Then click on Conditional formating and select Manage Rules, then new rules. Was told to select " format only cells that contain", cell value, less then and then enter =Now()>E7-30.

    Is this correct because it is not working, it continues to turn all of the cells red.

     

    Thanks for your patience and help!


    Lisa Marrone
    Friday, March 18, 2011 10:55 PM
  • You need to use the "Use a formula to determine which cell to format" option at the bottom.

    Put the formula in the box then click format and set the format you want.  Then click OK.

     


    G North MMI
    Friday, March 18, 2011 11:09 PM
  • Thanks for your help. Continues to turn the entire column red regardless of the dates. I give up!
    Lisa Marrone
    Monday, March 21, 2011 5:57 PM
  • OK

    Can we try this in a test workbook?

    • Open a new workbook.
    • In B1 enter 1/1/11.
    • Drag this date down to row 12.
    • On the AutoFill Options that appears after you have completed the drag.  Change the option to Fill Months.

    You should now have the the first of each month in the column.

    • Select all these dates
    • Go to Conditional Formating and create a New Rule.
    • Select the "Use a formula to determine which cell to format" option at the bottom of the list.
    • In the formula box type =NOW()>B1-30
    • Click the Format button and set a red background.
    • And click the OK button to get back to the work book.

    The first four months should now be red.

    Did you clear the rules from your original attempt? I know it's a silly question but the only thing I can think of is your dates aren't real dates.  However the rule would cause dates that are text not to colour at all.

    What happens to your dates if you change their format to General?  (1st of April 2011 would change to 40634)

    Can you send the file to me, if I gave you my info?


    G North MMI
    Monday, March 21, 2011 8:14 PM
  • Here is my info lmarrone@commoninterest.com If you would like to contact me I will send you my spreadsheet to check. I truly appreciate it!
    Lisa Marrone
    Tuesday, March 22, 2011 5:36 PM
  • Hi

    Check you inbox for a message.


    G North MMI
    Tuesday, March 22, 2011 6:23 PM
  • For anyone following this the formula was correct but the problem was caused by the column containing blanks that were formatting due to the conditional test.

    Revised the test formula to

    =and(now()>e7-30,len(e7)>0)

    Now the blank cells are ignored.


    G North MMI
    • Marked as answer by Sally Tang Friday, March 25, 2011 8:08 AM
    Tuesday, March 22, 2011 7:17 PM