Results 1 to 8 of 8

Thread: Conditional formatting to RAG rate based on dates

  1. #1

    Conditional formatting to RAG rate based on dates



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

    I have a column that holds a date (F2). This is the "received" date. Then i have another column (I2) that i work out and input a "target" date that is 20 working days after that in F2. What i want is when I2 is between 0 - 10 working days from F2 to be green, 11 - 20 working days to be amber, and when it's 21 days and over, to be red. Does that make sense?

    Obviously i would like the "fill" to be rag rated, not the font, but i don't know how to do that on here
    Many thanks in advance.
    Debbie.
    F2 I2
    04/09/16 30/09/16
    16/09/16 14/10/16
    25/09/16 21/10/16

  2. #2
    Administrator Pecoflyer's Avatar
    Join Date
    Oct 2011
    Location
    Brussels Belgium
    Posts
    1,673
    Articles
    0
    Excel Version
    2010 on Xubuntu
    Hello
    what is RAG rate please?
    Thank you Ken for this secure forum.

  3. #3
    Hello. Sorry, it's RED, AMBER, GREEN.

  4. #4
    Magician navic's Avatar
    Join Date
    Aug 2013
    Location
    Europe, Croatia
    Posts
    885
    Articles
    0
    Excel Version
    Excel 2013
    Quote Originally Posted by DebsTurner View Post
    Obviously i would like the "fill" to be rag rated, not the font

    If you had been set an example with the expected result, it would probably be clearer.
    Try Conditional Formatting
    GREEN
    Code:
    =DAY(E1)<11
    AMBER
    Code:
    =AND(DAY(E1)>10;DAY(E1)<21)
    RED
    Code:
    =DAY(E1)>20
    My personal Web Excel Tutorials
    Attention! In the formulas as a delimiter I use a semicolon (; ) if you using my formula, then perhaps you need to use a comma (,)
    Also for a decimal number I use a comma (,) you may need to use point (.) instead of

  5. #5
    Acolyte SUNNY KOW's Avatar
    Join Date
    Jul 2016
    Location
    Malaysia
    Posts
    41
    Articles
    0
    Hi Debs
    You can try Conditional Formatting
    e.g.

    =I2-F2>=21 for RED
    =AND(I2-F2>=11,I2-F2<=20) for AMBER
    =I2-F2<=10 for GREEN

    Refer attachment.
    Hope this is what you are looking for.
    Attached Files Attached Files

  6. #6
    Hi. Thanks both for your offer of help. It's much appreciated. I can't however get any of them to work. Do they recognise that it's "working days" i need, rather than a straight run?

  7. #7
    Magician navic's Avatar
    Join Date
    Aug 2013
    Location
    Europe, Croatia
    Posts
    885
    Articles
    0
    Excel Version
    Excel 2013

    Workday in Excel and Conditional Formatting

    Quote Originally Posted by DebsTurner View Post
    I can't however get any of them to work.
    Please put your workbook example with results expected.

    BTW: Try formula for calculation workday
    Code:
    =NETWORKDAYS(startDate;endDate)+IF(NETWORKDAYS(startDate;endDate)<0;1;-1)+1
    My personal Web Excel Tutorials
    Attention! In the formulas as a delimiter I use a semicolon (; ) if you using my formula, then perhaps you need to use a comma (,)
    Also for a decimal number I use a comma (,) you may need to use point (.) instead of

  8. #8
    OK. I've come to realise I've gone about this entirely the wrong way, and I've not explained myself very well at all. Thanks to those who have taken the time to post help thus far. This is what I want the formula to say in cell I2 -

    If today is greater than or equal to the date in F2 and is less than or equal to the date in H2, turn green.

    If today is equal to H2+1 but less than or equal to I2, turn amber.

    If today is greater than I2, turn red.

    God I hope that makes sense to somebody.

Tags for this Thread

Posting Permissions

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