Page 2 of 3 FirstFirst 1 2 3 LastLast
Results 11 to 20 of 24

Thread: Conditionnal Formating

  1. #11
    Acolyte Jlamarre's Avatar
    Join Date
    Nov 2018
    Posts
    22
    Articles
    0
    Excel Version
    2016


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

    Quote Originally Posted by navic View Post
    I do not understand you well.
    Can you set the expected results for a few examples of different dates (as well as the background color of the cell).

    Here is an updated file with some comments in the file.
    Attached Files Attached Files

  2. #12
    Magician navic's Avatar
    Join Date
    Aug 2013
    Location
    Europe, Croatia
    Posts
    885
    Articles
    0
    Excel Version
    Excel 2013
    It's still confusing.
    If I understood you well the Subtraction between two dates in the example is the next

    - Red
    Today Date - Condition Date = ? Days (This is Croatian format Date)
    6.12.2018 - 26.11.2018 = 10 days
    7.12.2018 - 26.11.2018 = 11 days

    - Yellow
    8.12.2018 - 26.11.2018 = 12 days
    9.12.2018 - 26.11.2018 = 13 days
    10.12.2018 - 26.11.2018 = 14 days

    - Green
    11.12.2018 - 26.11.2018 = 15 days
    12.12.2018 - 26.11.2018 = 16 days
    13.12.2018 - 26.11.2018 = 17 days


    So, you need this based on the number of past days, from the conditions in the 'A2' cell, highlight the 'I2' cell.

    Perhaps these formulas are appropriate for solving your problem.(Note! In these formulas below, the condition is in the 'A2' cell. If that's not the case, enter the 'I2' cell address or what you want already)

    - Conditional Formatting rule - Set the Red
    Code:
    =OR(TODAY()-$A2=10;TODAY()-$A2=11)
    - Conditional Formatting rule - Set the Yellow
    Code:
    =OR(TODAY()-$A2=12;TODAY()-$A2=13;TODAY()-$A2=14)
    - Conditional Formatting rule - Set the Green
    Code:
    =OR(TODAY()-$A2=15;TODAY()-$A2=16;TODAY()-$A2=17)
    Sorry if I did not understand you well, English is not my mother tongue.
    Attached Files Attached Files
    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

  3. #13
    Acolyte Jlamarre's Avatar
    Join Date
    Nov 2018
    Posts
    22
    Articles
    0
    Excel Version
    2016
    The only date that should be taken in consideration is the "I" column. All other dates should not be taken in consideration.

    In my understanding (that is not good obviously since I'm asking a lot of questions)

    I would have thought it would be like.

    an IF formula
    today-I2 is greater then 1 to 3 days in green
    4 to 6 days in yellow
    7 + days in red

    Hope this clears things

    Thank you

  4. #14
    Magician navic's Avatar
    Join Date
    Aug 2013
    Location
    Europe, Croatia
    Posts
    885
    Articles
    0
    Excel Version
    Excel 2013
    Quote Originally Posted by Jlamarre View Post
    an IF formula
    today-I2 is greater then 1 to 3 days in green
    4 to 6 days in yellow
    7 + days in red
    Select all Date cells in 'I' column. Apply CF and formulas

    - Yellow
    Code:
    =OR(TODAY()-$I2=1;TODAY()-$I2=2;TODAY()-$I2=3)
    - Green
    Code:
    =OR(TODAY()-$I2=4;TODAY()-$I2=5;TODAY()-$I2=6)
    - Red
    Code:
    =TODAY()-$I2>=7
    Is that a solution?
    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. #15
    Magician navic's Avatar
    Join Date
    Aug 2013
    Location
    Europe, Croatia
    Posts
    885
    Articles
    0
    Excel Version
    Excel 2013
    My apology, I made a mistake in the color order settings.
    The order of the formula is following.
    - Green
    - Yellow
    - Red
    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

  6. #16
    Acolyte Jlamarre's Avatar
    Join Date
    Nov 2018
    Posts
    22
    Articles
    0
    Excel Version
    2016
    Quote Originally Posted by navic View Post
    Select all Date cells in 'I' column. Apply CF and formulas

    - Yellow
    Code:
    =OR(TODAY()-$I2=1;TODAY()-$I2=2;TODAY()-$I2=3)
    - Green
    Code:
    =OR(TODAY()-$I2=4;TODAY()-$I2=5;TODAY()-$I2=6)
    - Red
    Code:
    =TODAY()-$I2>=7
    Is that a solution?
    This did not seem to work. I don't really understand why that is.

    question about the formula that you suggested. Would it be simpler to use today-thecell=<3?

    The one in yellow might need all the units 4,5,6

    And red
    >7

    I'm just trying to understand and learn. In no way I'm trying to be arrogant.

    Sent from my SM-G955W using Tapatalk

  7. #17
    Magician navic's Avatar
    Join Date
    Aug 2013
    Location
    Europe, Croatia
    Posts
    885
    Articles
    0
    Excel Version
    Excel 2013
    Quote Originally Posted by Jlamarre View Post
    Would it be simpler to use today-thecell=<3?
    OK if you want, try

    - Green
    Code:
    =AND(TODAY()-$I2>0;TODAY()-$I2<=3)
    - Yellow
    Code:
    =AND(TODAY()-$I2>3;TODAY()-$I2<=6)
    - Red
    Code:
    =TODAY()-$I2>=7
    Attached Files Attached Files
    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. #18
    Acolyte Jlamarre's Avatar
    Join Date
    Nov 2018
    Posts
    22
    Articles
    0
    Excel Version
    2016
    Thank you.

    This works perfectly.



    Sent from my SM-G955W using Tapatalk
    Last edited by Pecoflyer; 2018-12-16 at 07:32 AM. Reason: Removed unnecessary quotes

  9. #19
    Magician navic's Avatar
    Join Date
    Aug 2013
    Location
    Europe, Croatia
    Posts
    885
    Articles
    0
    Excel Version
    Excel 2013
    You're welcome.
    But!
    Quote Originally Posted by Jlamarre View Post
    This did not seem to work.
    I do not understand how the Workbook did not work with the OR function. The result is the same. Look at the last attachment. There you have two sheets (OR sheet, AND sheet)
    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

  10. #20
    Administrator Pecoflyer's Avatar
    Join Date
    Oct 2011
    Location
    Brussels Belgium
    Posts
    1,673
    Articles
    0
    Excel Version
    2010 on Xubuntu
    @navic
    From my experience it frequently happens that while entering formulas in CF, one uses the left or right arrow, which, as you know, scrambles the formula. Perhaps that is what happened here?
    Thank you Ken for this secure forum.

Page 2 of 3 FirstFirst 1 2 3 LastLast

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
  •