Results 1 to 8 of 8
  1. #1
    2 Star Lounger
    Join Date
    May 2002
    Posts
    155
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Conditional Formatting with Dates (Excel 2000 SP3)

    I have a worksheet with 2 tables. The top table is a contract programme which has dates across the top, and tasks down the side. The dates are in the "Custom" format of dd/mm/yy and the date cells have also been formatted to read vertically (ie. 90 degrees from normal) though I don't think that is relevant.

    The second table lists theoretical and actual outputs etc, and performs various calculations to end up with an actual finish date in the right hand column. This date is again in dd/mm/yy format, and will change as the actual outputs achieved figures are updated.

    I want to use conditional formatting to apply to all the dates across the top of my programme, such that the actual finish date is highlighted in a colour. As the actual outputs are updated in the lower table, and the actual finish date calculated within that table changes, I want the highlighted cell in the programme to change accordingly.

    So my conditional format wants to be something like if the (date) value in this cell is the same as the (date) result in cell (say) $K$18, highlight this cell in red.

    I understand the theory, but I can't get it to work in practise....

    Help! (please)

    Neil

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

    Re: Conditional Formatting with Dates (Excel 2000 SP3)

    Do you have a single finish date in the second table, or a column with finish dates?

  3. #3
    2 Star Lounger
    Join Date
    May 2002
    Posts
    155
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Conditional Formatting with Dates (Excel 2000 SP3)

    I actually have a column with different finish dates (the rows again representing different elements of work).

    However, if I could get this to work just referencing one specific cell from that range, it would be very useful.

    In an ideal world, I suppose getting the conditional format to match to the latest of the range of dates in the righthand column of the lower table would be the best outcome, but that sounds to me like one complication too many!!

    I await your solution with eager anticipation, Hans! (and thank you for your interest, as ever).

    Neil

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

    Re: Conditional Formatting with Dates (Excel 2000 SP3)

    - Select the cells with the dates along the top of the upper table.
    - In the following, I'll assume that A1 is the active cell in the selection.
    - I'll also assume that the range with dates in the lower table is AF20:AF26.
    - Select Format | Conditional Formatting...
    - Select Formula Is from the first dropdown.
    - Enter the following formula in the box next to it, adjusting as needed:

    =A1=MAX($AF$20:$AF$26)

    - Note the use of relative addressing for the active cell, and absolute addressing for the column in the lower table.
    - Click Format...
    - Specify the formatting you want.
    - OK your way out.

    See the attached workbook (just a barebones example).

  5. #5
    2 Star Lounger
    Join Date
    May 2002
    Posts
    155
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Conditional Formatting with Dates (Excel 2000 SP3)

    Thanks Hans

    I've tried this, but it isn't working on my spreadsheet. At first I thought it was because the formula in my lower table was returning a word in some cases (and a date in others), but after I restricted the range in Table 2 to the 2 cells which have dates in, it still didn't work.

    I'm attaching the file, if you have a spare minute to take a look, please?

    My only other theory is that the lower table has some merged cells, and that might be throwing things out?

    Neil

  6. #6
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Conditional Formatting with Dates (Excel 2000 SP3)

    Hi,
    The problem is that your dates in the lower table contain fractional portions of days so the date headers do not exactly match up. If you are only interested in the day portion, change your conditional formatting formula to:
    <code>=C4=INT(MAX($CI$21:$CI$22))</code>
    assuming C4 is the active cell at the time.
    HTH
    Regards,
    Rory

    Microsoft MVP - Excel

  7. #7
    2 Star Lounger
    Join Date
    May 2002
    Posts
    155
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Conditional Formatting with Dates (Excel 2000 SP3)

    That solved it!

    Sincere thanks, Rory (and Hans). My colleague who asked if I knew how to do this is now seriously impressed!

    (I might even divulge my sources, once I've revelled in the moment a little longer).

    Cheers

    Neil

  8. #8
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Conditional Formatting with Dates (Excel 2000 SP3)

    Neil,
    We won't tell if you won't! Never reveal your sources and all that.. <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    Regards,
    Rory

    Microsoft MVP - Excel

Posting Permissions

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