none
Excel 2010 - Conditional formatting with IF Function

    Question

  • Hi

    In a workbook I have...

    A3 - contract expiry date

    B3 - notice period in days (e.g. 90)

    C3 - Renewal action date which is A3-B3

    It would like the figure in cell C3 to flag up in red when it is 30 days before the renewal date probably using an IF function and today's date.

    I was thinking of using conditional formatting but can't seem to get it to work. Any suggestions will be very helpful.

    Thanks, Viv


    Viv Haig

    Thursday, June 21, 2012 3:36 PM

Answers

  • Hi I've just worked it out.

    =(C3-TODAY()<30)

    Anybody got anything different?

    Viv


    Viv Haig

    • Marked as answer by VHaig Wednesday, June 27, 2012 8:32 AM
    Thursday, June 21, 2012 3:49 PM

All replies

  • Hi I've just worked it out.

    =(C3-TODAY()<30)

    Anybody got anything different?

    Viv


    Viv Haig

    • Marked as answer by VHaig Wednesday, June 27, 2012 8:32 AM
    Thursday, June 21, 2012 3:49 PM
  • If you want to highlight C3 when it is exactly 30 days after today's date:

    • Select C3.
    • On the Home tab of the ribbon, click Conditional Formatting > New Rule...
    • Select 'Format only cells that contain'.
    • Select 'Cell Value' in the first dropdown, 'equal to' in the second one, and enter =TODAY()+30 in the box next to it.
    • Click Format...
    • Specify the font or fill color that you want, then click OK twice.

    If you want to highlight C3 when it is 30 days or less after today's date, do the same but select 'less than or equal to' instead of 'equal to'.


    Regards, Hans Vogelaar

    Thursday, June 21, 2012 3:57 PM
  • Hi,

    Just checking in to see if the suggestion was helpful. Please let us know if you would like further assistance.


    Best Regards, 


    Rex Zhang

    TechNet Community Support

    • Marked as answer by VHaig Wednesday, June 27, 2012 8:32 AM
    • Unmarked as answer by VHaig Monday, July 02, 2012 3:19 PM
    Tuesday, June 26, 2012 1:36 AM
  • Thanks Hans.

    Viv Haig

    Wednesday, June 27, 2012 8:33 AM
  • Hi

    I have an addition to the original query. It would like the figure in cell C3 to flag up in red when it is 30 days before the renewal date and flag up in green when it is greater than 30 and less than or equal to 45.

    I've tried but I just can't get it to work. Any suggestions welcome.

    Viv


    Viv Haig

    Monday, July 02, 2012 3:31 PM
    • Select C3.
    • On the Home tab of the ribbon, click Conditional Formatting > Manage Rules...
    • Click New Rule...
    • Select 'Format only cells that contain'.
    • Select 'Cell Value' in the first dropdown, 'less than or equal to' in the second one, and enter =TODAY()+45 in the box next to it.
    • Click Format...
    • Specify green as fill color, then click OK twice.
    • Click New Rule...
    • Select 'Format only cells that contain'.
    • Select 'Cell Value' in the first dropdown, 'less than or equal to' in the second one, and enter =TODAY()+30 in the box next to it.
    • Click Format...
    • Specify red as fill color, then click OK twice.
    • Click OK to close the Conditional Formatting Rules Manager.

    Regards, Hans Vogelaar

    Monday, July 02, 2012 3:43 PM
  • Thanks Hans, but it's not quite what I'm looking for.

    The first formula I used =(C3-TODAY()<30) worked perfectly. I would like the cell ontain in C3 (Renewal Action Date) to apprear in red when it is less than 30 days from todays date (I got this to work) and green when it is greater than 30 and less than or equal to 45. It all depends on the date in cell C3 (in this example it's the first cell with 30-Nov-11 in) as there is a formula which takes the Contract Expires Date and deducts the Renewal Action Date.

    Does that make sense, maybe I'm not explaining it clearly? I think I'm trying to over complicate things and I'm maybe not following your solution. Any thoughts would be welcome.

    Viv 


    Viv Haig

    Tuesday, July 03, 2012 10:23 AM
  • You also need a rule to tell Excel not to color blank cells, or perhaps cells containing an Action Renewal Date before today.

    I have uploaded a small sample workbook ActionDates to SkyDrive: http://sdrv.ms/MFLPLE

    Please take a look, and if it doesn't do what you want, please post back.


    Regards, Hans Vogelaar

    Tuesday, July 03, 2012 11:39 AM
  • Hans I really appreciate your time with this. I'll try it out on my workbook and I'll get back to you.

    Many thanks, Viv


    Viv Haig

    Tuesday, July 03, 2012 1:30 PM