Results 1 to 5 of 5

Thread: Conditional Formatting

  1. #1

    Conditional Formatting



    Register for a FREE account, and/
    or Log in to avoid these ads!

    I'm trying to create conditional formatting that will do the following:
    A3= today's date I10:


    Anything due before today's date= Red
    Due within the next week: Anything due within 5 business days of A3= Yellow
    Due in the future: Anything due beyond 5 business days of A3= Green

    I've created the following conditional formatting rules:
    =IF($A$3>WORKDAY(I10,2,$M$5:$M$6),TRUE,FALSE) [will be red]
    =IF($A$3>=WORKDAY(I10,2,$M$5:$M$7),TRUE,FALSE) [will be yellow]
    =IF($A$3<=WORKDAY(I10,6,$M$5:$M$6),TRUE,FALSE) [will be green]

    I also need a way to count the amount of reds, yellows, and greens, without using a macro. I have used this formula, but it's not working properly: =IF(J10="","",IF(J10="Complete",4,IF(J10<$A$3,1,IF(J10=$A$3+(WORKDAY(I10,6,$M$5:$M$6)),3))))) Upon receipt, I will give the file.

    Any help?

  2. #2
    Administrator Simon Lloyd's Avatar
    Join Date
    Apr 2011
    Location
    Manchester, England
    Posts
    323
    Articles
    0
    Tons of help and it's all found here http://www.xldynamic.com/source/xld.html this is Bob Phillips site, you'll finde everything you need there
    Kind regards,
    Simon Lloyd
    Microsoft Office Discussion

  3. #3
    As Simon says, there is plenty there, even one section on traffic lighting due dates, http://www.xldynamic.com/source/xld.CF.html#due

  4. #4
    The site doesn't specify how to include only workdays in the function. I want to only include workdays and holidays.

    Also it doesn't specify how to break out the days. For example:

    Anything due before today's date= Red
    Due within the next week: Anything due within 5 business days of A3= Yellow
    Due in the future: Anything due beyond 5 business days of A3= Green

    How do I do that?

  5. #5
    Conjurer Roger Govier's Avatar
    Join Date
    Mar 2011
    Location
    Located near Abergavenny, South Wales, UK
    Posts
    113
    Articles
    0
    Hi

    Just substitute your formulae for the ones Bob uses in his example.
    You don't need the whole of your formulae however, miss out the IF statements
    =$A$3>WORKDAY(I10,2,$M$5:$M$6) [will be red]
    =$A$3>=WORKDAY(I10,2,$M$5:$M$7) [will be yellow]
    =$A$3<=WORKDAY(I10,6,$M$5:$M$6) [will be green]

    The formulae themselves will return a True or False which the CF will use to determine whether to colour the cell.
    Regards

    Roger Govier
    Microsoft Excel MVP
    http://www.technology4u.co.uk

Posting Permissions

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