Results 1 to 3 of 3
  1. #1
    Star Lounger
    Join Date
    Nov 2004
    Location
    San Diego, California, USA
    Posts
    95
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Conditional Format Problem (2000 SR1)

    I have created an XL file with links (~10 rows) from 2 MS Project plans. The links contain dates that I would like to "shade" (i.e., using background color), depending on whether the projected dates look like they will meet our drop-dead-dates (DDD), or not. I have entered 3 conditions in all of these date cells with dates, and a row at the top that contains our target (DDD) date - see attachment.

    General logic:
    Delivery Date (linked data) < Target Date (set green beckground)
    Delivery Date (linked data) = Target Date (set yellow background)
    Delivery Date (linked data) > Target Date (set red background)

    This worked OK when I tested it on data that was entered into an XL file, but doesn't work properly for the linked data. Cells are either green or red, but no yellows. The cells which have dates that are equal are turned red. Am I having a date formatting issue of some kind where I need to format the cells to truncate the hours/minutes off of the date field from the project plan? If so, can this be accomplished better with a macro? I would prefer that this be completely automated, where no one has to manually run any macros.

    I would appreciate any help or direction.

    Thank you!
    Don Liebman
    San Diego, CA 92115

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Conditional Format Problem (2000 SR1)

    If you format the cells with linked dates to display both date and time, do they have a non-zero time part? If so, is it a matter of a few seconds (probably a rounding error)? You can test whether the problem is due to rounding errors by changing condition #2 to

    Cell Value Is less than =$C$3+10/86400

    This adds 10 seconds to the date in C3.

  3. #3
    Star Lounger
    Join Date
    Nov 2004
    Location
    San Diego, California, USA
    Posts
    95
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Conditional Format Problem (2000 SR1)

    Hans,

    I checked the couple of dates that should meet the "equals" condition, and they do have non-zero times (e.g., 3/1/05 5:00 PM) and are definitely not rounding issues. I will try using the "between" test instead to check for the entire 24-hr period.

    Thanks!
    Don Liebman
    San Diego, CA 92115

Posting Permissions

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