+ Reply to Thread
Results 1 to 10 of 10

Conditional Formating - change colour to specific days

  1. #1
    Registered User
    Join Date
    05-24-2023
    Location
    London
    MS-Off Ver
    Microsoft Office Professional Plus 2016
    Posts
    5

    Conditional Formating - change colour to specific days

    Hi guys

    I have a spreadsheet with a number of dates. Some of the dates are in the futhure some in the past.


    I need help with Conditional Formating, for example.


    I need the date to highlight to:
    Green when more than 60 days left to date in the cell
    Orange when there is more than 1 but less than 60 days left
    Red when the date has passed.

    Please help as I am trying to solve this for the last 3h

  2. #2
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.83 (24031120))
    Posts
    8,684

    Re: Conditional Formating - change colour to specific days

    a sample sheet would help to see the data, and what cells

    but basically

    RED
    AND(cell with date < today, cell with date <>"")

    yellow
    and(cell with date >=today() , cell with date <=today()+60)

    green
    cell with date <today()+60

    you can change the = to match if you want that day included

    for 2007, 2010 , 2013 , 2016 , 2019 or 365 Subscription excel version
    Conditional Formatting

    Highlight applicable range >>
    A2:A100 - Change, reduce or extend the rows to meet your data range of rows

    Home Tab >> Styles >> Conditional Formatting
    New Rule >> Use a formula to determine which cells to format
    Edit the Rule Description: Format values where this formula is true:
    and(a2 >=today() , a2 <=today()+60)

    Format [Number, Font, Border, Fill]
    choose the format you would like to apply when the condition is true
    OK >> OK




    A sample sheet would help here, and possibly will enable a quicker and more accurate solution for you.


    The forum does allow for spreadsheets to be uploaded direct to the forum -

    Please see the Yellow Banner at the top of the page explaining how to attach a sample spreadsheet

    A good sample workbook has just 10-20 rows of representative data that has been desensitised. It also has expected results mocked up, relevant cells highlighted and a few explanatory notes.

    Here are the instructions, found at the top of the page again
    Post a small Excel sheet (not a picture) showing realistic & representative sample data WITHOUT confidential information (10-20 rows, not thousands...) and some manually calculated results. For a new thread (1st post), scroll to Manage Attachments, otherwise scroll down to GO ADVANCED, click, and then scroll down to MANAGE ATTACHMENTS and click again. Now follow the instructions at the top of that screen.
    Attached Files Attached Files
    Wayne
    if my assistance has helped, and only if you wish to , there is an "* Add Reputation" on the left hand side - you can add to my reputation here

    If you have a solution to your thread - Please mark your thread solved do the following: >
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

  3. #3
    Registered User
    Join Date
    05-24-2023
    Location
    London
    MS-Off Ver
    Microsoft Office Professional Plus 2016
    Posts
    5

    Re: Conditional Formating - change colour to specific days

    Hi

    Thank you very much for your help, I tried the formulas, and the cells change colours. But I dont think it calculates them correctly, I think either I am doing something wrong.

    The column D the formula is not finished yet, I want to make it:
    - 90+ days from Column C if Column A has Under Review with RM - TRADE and Column B is D - Medium High or E - High
    - and +180 days if Column A has Under Review with RM - TRADE and Column B is A - Low, B- Medium Low, C - Medium.

    Then based on the dates I am going to get, the colours should change.
    Attached Files Attached Files

  4. #4
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.83 (24031120))
    Posts
    8,684

    Re: Conditional Formating - change colour to specific days

    originally you mentioned 60 days

    now its 90 and 180 - which is why the conditional formatting you have entered does not work
    also worth having a normal spreadsheet all editable - i can then show the examples for you

    so
    you have selected the range
    $D$1,$D$5:$D$1048576

    =AND(D5<>"""",D5<=TODAY())
    works ok for RED I assume

    $D$5:$D$1048576
    bUT the formula starts at D9 - needs to be D5 - same as range
    =AND(D9>TODAY(), D9<=TODAY()+60)


    select D5:D1000 - or whatever the range you need
    Now we are testing column C

    SO
    red
    =AND($C5<>"",$C5<=TODAY())

    YELLOW
    - 90+ days from Column C if Column A has Under Review with RM - TRADE and Column B is D - Medium High or E - High

    =AND( $C5>=TODAY()+90, $A5="RM - TRADE" ,OR($B5="D - Medium High",$B5= "E - High"))

    GREEN

    - and +180 days if Column A has Under Review with RM - TRADE and Column B is A - Low, B- Medium Low, $B5=.

    =AND( $C5>=TODAY()+180, $A5="RM - TRADE" ,OR($B5="A - Low",$B5= "B- Medium Low",$B5= "C - Medium "))

    Use stop if true and put the 180 first


    can you attach a smaller sample , as this one wants to update , has restricted columns and is just slow to do anything with

  5. #5
    Registered User
    Join Date
    05-24-2023
    Location
    London
    MS-Off Ver
    Microsoft Office Professional Plus 2016
    Posts
    5

    Re: Conditional Formating - change colour to specific days

    Good morning

    I have corrected the formulas and works perfect, colours matches the date. Thank you very much for your help.

    The part I have left is Column D, how do I add to the existing formula if it is not "Under Review with RM - TRADE", then leave the cell blank or N/A ? so it doesnt show any date.

    I attach the corrected and shorter database.
    Attached Files Attached Files
    Last edited by dawidmns; 05-25-2023 at 05:27 AM.

  6. #6
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.83 (24031120))
    Posts
    8,684

    Re: Conditional Formating - change colour to specific days

    would you clarify the dates now

    yellow is greater than today , but less than 90 days in future - if so , no examples in column C that meet that

    i'm just not sure of the dates and examples , dates based on column C

    red is ok,
    Red when the date has passed.
    anything earlier than today

    which means i assume the 90 days - yellow is in the future

    so yellow anything between today - which is 25th May and +90 days is 23/8/23
    green is 23/8/23 to 11/11/23

    and then anything in future - later than 11/11/23 is not formatted

    or have i got that all wrong


    i have added 3 columns I,J,K
    with each criteria to text
    K is the date

    ALSO its in contradiction to your first post with dates and 60days
    also still errors in the conditional formatting in example
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    05-24-2023
    Location
    London
    MS-Off Ver
    Microsoft Office Professional Plus 2016
    Posts
    5

    Re: Conditional Formating - change colour to specific days

    Hi

    Spoke with my collegues and with the days, they want to leave it as it is now, so column D no need to change the dates. I only need to add to the existing formula if it is not "Under Review with RM - TRADE", then leave the cell blank or N/A ? so it doesnt show any date. So the dates for colours will stay the same

    Green when more than 60 days left to date in the cell
    Orange when there is more than 1 but less than 60 days left
    Red when the date has passed.


    However, when I transferred the formulas for colours to the original spreadsheet, updated the cells. There is something wrong and red colour is highlighting even the dates which are in the future.

  8. #8
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.83 (24031120))
    Posts
    8,684

    Re: Conditional Formating - change colour to specific days

    in your example NO dates are greater than today() - so it will ALL be red

    if any thing with
    "Under Review with RM - TRADE"
    Will NOT be coloured

    The dates you want to base the colour on - are in column C or D - contradicting again

    so the RULE for RED
    change the column for dates to either C or D

    Select D5:D10000 - or however many rows you need - BUT it needs to be row 5 to match the formula

    RED
    =AND($D5<>"",$D5<=TODAY(),$A5<>"Under Review with RM - TRADE")

    YELLOW
    =AND($D5>TODAY(), $D5<=TODAY()+60,$A5<>"Under Review with RM - TRADE")

    GREEN
    =AND($D5>TODAY()+60,$A5<>"Under Review with RM - TRADE")

    if you want to use column C for the dates then Change the $D5 to $C5

    around 18:00 BST 25th may - today , i will not be around for maybe a little over a week - friday 2nd June
    Attached Files Attached Files

  9. #9
    Registered User
    Join Date
    05-24-2023
    Location
    London
    MS-Off Ver
    Microsoft Office Professional Plus 2016
    Posts
    5

    Re: Conditional Formating - change colour to specific days

    I have tried the formulas on the original spreadsheet and it works perfect! However, I had to remove criteria "Under Review with RM - Trade" as it didnt work. But, we just come up with different idea that we will try to test and see how it goes.

    I really want to thank you for your support, I really appreciate your help and time. Really happy it is done now as I struggled for some time now.

  10. #10
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.83 (24031120))
    Posts
    8,684

    Re: Conditional Formating - change colour to specific days

    you are welcome

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Conditional Formatting based on partial/specific text to Change formating
    By mh348 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 01-23-2021, 10:36 AM
  2. [SOLVED] to change shape colour by conditional formating/ VBA
    By asmi in forum Excel Programming / VBA / Macros
    Replies: 19
    Last Post: 11-08-2013, 03:57 AM
  3. [SOLVED] Excel - conditional formating on graphs. Change colour on a value
    By Peter Shore in forum Excel Charting & Pivots
    Replies: 7
    Last Post: 07-09-2013, 06:00 AM
  4. Replies: 3
    Last Post: 05-22-2012, 03:26 PM
  5. Conditional Formating-change colour
    By vandanavai in forum Excel General
    Replies: 1
    Last Post: 01-26-2007, 05:24 AM
  6. Conditional Formating-change the cell colour to green
    By stevekirk in forum Excel General
    Replies: 4
    Last Post: 10-20-2006, 02:52 AM
  7. Conditional formating-change colour when D30 is equal
    By smith.james0 in forum Excel General
    Replies: 2
    Last Post: 07-26-2005, 01:05 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1