Page 1 of 3 1 2 3 LastLast
Results 1 to 10 of 24

Thread: Conditionnal Formating

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

    Question Conditionnal Formating



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

    Hello group,

    One question. I would like to have the color of the cell change if the date in the cell is lower then todays date. I would like two colors. Yellow if the date is within now and -3 days and red if greater then 3 days.

    Thank you

    JL

  2. #2
    Administrator Pecoflyer's Avatar
    Join Date
    Oct 2011
    Location
    Brussels Belgium
    Posts
    1,655
    Articles
    0
    Excel Version
    2010 on Xubuntu
    Try =A1<=today()-3 for yellow
    and =A1>today()+3 for red
    as CF

    ( If I understood you correctly)
    Thank you Ken for this secure forum.

  3. #3
    Acolyte Jlamarre's Avatar
    Join Date
    Nov 2018
    Posts
    22
    Articles
    0
    Excel Version
    2016
    In this column date of transaction
    2018-12-05
    2018-12-11
    Do I need to have a cell with todays date so that the formula can point to?

    Thank you

  4. #4
    Magician navic's Avatar
    Join Date
    Aug 2013
    Location
    Europe, Croatia
    Posts
    884
    Articles
    0
    Excel Version
    Excel 2013
    Quote Originally Posted by Jlamarre View Post
    Do I need to have a cell with todays date......?
    No.
    Select the range of cells in the column, in which you want to highlight the cells.
    Enter formulas for Conditional Formatting (in the field 'Use a formula to determine the required cells to format' and define color).
    btw: If this is not clear, set an example of your workbook
    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
    Administrator Pecoflyer's Avatar
    Join Date
    Oct 2011
    Location
    Brussels Belgium
    Posts
    1,655
    Articles
    0
    Excel Version
    2010 on Xubuntu
    As navic said, you don't have to use a cell with today's date. But as today() is a volatile function, it could be good practice in some cases
    Thank you Ken for this secure forum.

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

    File

    Here is the file (part).

    Thank you
    Attached Files Attached Files

  7. #7
    Super Moderator JoePublic's Avatar
    Join Date
    Sep 2011
    Location
    Askew
    Posts
    194
    Articles
    0
    Excel Version
    2010
    Quote Originally Posted by Pecoflyer View Post
    But as today() is a volatile function, it could be good practice in some cases
    Conditional formatting is inherently volatile, so I don't think it would make any difference.
    This website wants to know your momentum - | Deny | | Allow |

  8. #8
    Magician navic's Avatar
    Join Date
    Aug 2013
    Location
    Europe, Croatia
    Posts
    884
    Articles
    0
    Excel Version
    Excel 2013
    Please see attached example
    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

  9. #9
    Acolyte Jlamarre's Avatar
    Join Date
    Nov 2018
    Posts
    22
    Articles
    0
    Excel Version
    2016
    Quote Originally Posted by navic View Post
    Please see attached example
    Thank you Navic,

    But that is not what I am trying to do.
    In A is the date the Item in B was asked. According to some factors I will order the items or I will wait. The date in I is the ordered date. If that date takes to much time to receive I would like the format to change. A dot or the color of the cell like you did is fine.

    Thank you again for your time.

    JL

  10. #10
    Magician navic's Avatar
    Join Date
    Aug 2013
    Location
    Europe, Croatia
    Posts
    884
    Articles
    0
    Excel Version
    Excel 2013
    Quote Originally Posted by Jlamarre View Post
    But that is not what I am trying to do.
    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).
    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

Page 1 of 3 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
  •