Results 1 to 7 of 7
  1. #1
    3 Star Lounger
    Join Date
    Jul 2007
    Location
    United States
    Posts
    220
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Conditional Formatting Formula (MS Excel 2003)

    I can't figure out the equation for a conditional formatting formula. I have a start date and a completion date. I want the completion date cell to format green if the completion date is within 2 years of the start date. I have attached a small table with data and formatting that is wrong, but just requires a formula change.

  2. #2
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Conditional Formatting Formula (MS Excel 2003)

    In cell B2 set the conditional formatting formula to:

    =DATEDIF($A2,$B2,"y")<=2

    and copy the format down.
    -John ... I float in liquid gardens
    UTC -7ąDS

  3. #3
    3 Star Lounger
    Join Date
    Jul 2007
    Location
    United States
    Posts
    220
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Conditional Formatting Formula (MS Excel 2003)

    Okay, that worked...almost. I changed the date criteria to less than 25 months. For some reason that does a better job than less than or equal to 2 years. I don't really understand that.

    However, if I have blank cells in column A, how do I turn off the formatting? Take a look at the new attachment.

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Conditional Formatting Formula (MS Excel 2003)

    The 2 years is very literal and works witht he same month and even the day. What dates did the 2 years not work for?

    As to eliminating blanks:
    =AND(B2<>"",DATEDIF($A2,$B2,"m")<25)

    Steve

  5. #5
    3 Star Lounger
    Join Date
    Jul 2007
    Location
    United States
    Posts
    220
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Conditional Formatting Formula (MS Excel 2003)

    I didn't apply it to the example data table that I provided. I needed help getting started. I used the formula with some additional criteria. Using months and years usually brings back a weird value. For instance, less than or equal to 2 years returns anything that is a day short of 3 years. I am just converting everything to days ("d") and using the 365.24 as the year value.

  6. #6
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Conditional Formatting Formula (MS Excel 2003)

    You could use something like this, I'm not sure it will yield a more precise result from =DATEDIF(start,complete,"d")

    =AND(ISNUMBER(B2),DATE(YEAR(B2)-2,MONTH(B2),DAY(B2))>A2)
    -John ... I float in liquid gardens
    UTC -7ąDS

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Conditional Formatting Formula (MS Excel 2003)

    <hr> For instance, less than or equal to 2 years returns anything that is a day short of 3 years.<hr>

    And this is to be expected. I am 49 yrs old and will be 49 until the end of this year on my brithday. Age is a "step function" and DATEDIF is a reflection of that.. [You could use <2 years and just add 1 to the comparison...]

    Steve

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •