+ Reply to Thread
Results 1 to 14 of 14

Conditional Formatting - change color based on how far/close date is from today

  1. #1
    Registered User
    Join Date
    08-03-2015
    Location
    United States
    MS-Off Ver
    Excel 2013
    Posts
    38

    Lightbulb Conditional Formatting - change color based on how far/close date is from today

    Hello,

    I need help with conditional formatting. I have an excel worksheet where I track orders that have been placed and any information regarding these orders, e.g., date it was submitted [Column A], Estimated Ship Date (ESD) [F], Ship date [G], Tracking [H], Estimated Time of Arrival (ETA) [I], etc. To help me prompt with what I need to do next, as there are a lot of orders to keep track of, I would like these cells to change color as these dates approach/have past.

    1) I would like the cell in the ESD column (F) to change color if it is 5 days past the date it was submitted (5 days past date in column A). Then I would like it to change back if the cell in the ESD column is no longer blank.

    2) I would like the cell in the Ship Date column (G) to change color if today is after the date in the ESD column (F). Then I would like it to change back if the cell in the Ship Date column is no longer blank.

    3) I would like the ETA column (I) to color scale - darker if the date in this column is closer to today's date and lighter the further it is from today's date. Then I would like it change back if the cell in this column is no longer blank.

    Please help!

    Thank you

  2. #2
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    24,928

    Re: Conditional Formatting - change color based on how far/close date is from today

    Can you post a small sample file showing expected outcomes. This allows respondents to test any solutions that are offered. Thank you.

  3. #3
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,341

    Re: Conditional Formatting - change color based on how far/close date is from today

    Cant give exact details because you did not give much to work on, but play with this and see how you make out...

    1. highlight the range you want to apply the conditional formatting to
    2. on the home tab, styles, select CF
    3. select new rule, select use formula
    4. enter =(and(F2>=TODAY()+5,.......
    hmm wait, you said highlight if the date in that cell is 5 days past today, but you want to to change back if it is not empty? If it is not empty, it will have a date, no?

    That formula was going to be...
    =(and(F2>=TODAY()+5,"ESD"<>"")
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  4. #4
    Registered User
    Join Date
    08-03-2015
    Location
    United States
    MS-Off Ver
    Excel 2013
    Posts
    38

    Re: Conditional Formatting - change color based on how far/close date is from today

    Quote Originally Posted by JohnTopley View Post
    Can you post a small sample file showing expected outcomes. This allows respondents to test any solutions that are offered. Thank you.
    Hello,

    I have attached a sample chart for your reference. Thank you so much!
    Attached Files Attached Files

  5. #5
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    24,928

    Re: Conditional Formatting - change color based on how far/close date is from today

    For 1).

    =AND(TODAY()+5>$A3,$F3=0)

    for 2),

    =AND(G3="",F3<>"",TODAY()>F3)

    3)

    What date do we compare against ?
    Last edited by JohnTopley; 12-10-2015 at 04:39 AM.

  6. #6
    Registered User
    Join Date
    08-03-2015
    Location
    United States
    MS-Off Ver
    Excel 2013
    Posts
    38

    Re: Conditional Formatting - change color based on how far/close date is from today

    Quote Originally Posted by JohnTopley View Post
    For 1).

    =AND(TODAY()+5>$A3,$F3=0)

    for 2),

    =AND(G3="",F3<>"",TODAY()>F3)

    3)

    What date do we compare against ?
    Hello John,

    Thank you for your response - I will try the first two right now. For number 3), I'm not sure I understand your question. Let me revise what I need for number 3: 3) I would like the ETA column (I) to color scale - darker if the date in this column is closer to today's date and lighter the further it is from today's date.

    Let me know if you need more information.

  7. #7
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,341

    Re: Conditional Formatting - change color based on how far/close date is from today

    Did you see my comment in post #3?
    hmm wait, you said highlight if the date in that cell is 5 days past today, but you want to to change back if it is not empty? If it is not empty, it will have a date, no?

  8. #8
    Registered User
    Join Date
    08-03-2015
    Location
    United States
    MS-Off Ver
    Excel 2013
    Posts
    38

    Re: Conditional Formatting - change color based on how far/close date is from today

    Quote Originally Posted by JohnTopley View Post
    For 1).

    =AND(TODAY()+5>$A3,$F3=0)

    for 2),

    =AND(G3="",F3<>"",TODAY()>F3)

    3)

    What date do we compare against ?
    Also,

    I tried the two formulas you gave me. The one for #2 works fine - but the one for #1 highlights the cell even when it is less than 5 days past the day - please help!

    THANK YOU SO MUCH!!!!

  9. #9
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    24,928

    Re: Conditional Formatting - change color based on how far/close date is from today

    For 3

    =AND(I3<>"",I3>TODAY()-5) lighter

    =AND(I3<>"",I3>TODAY()-3) darker

  10. #10
    Registered User
    Join Date
    08-03-2015
    Location
    United States
    MS-Off Ver
    Excel 2013
    Posts
    38

    Re: Conditional Formatting - change color based on how far/close date is from today

    Quote Originally Posted by FDibbins View Post
    Did you see my comment in post #3?
    Hello,

    Thank you for your response. I apologize for the confusion; let me rephrase my request: I would like the ETA column (I) to color scale, with warmer/darker colors for the dates in this column closest to TODAY's date (into the future), and then lighter colors for the cells in this column with dates that are furthest from TODAY's date (into the future). Once the "Complete Date" column (L) is no longer blank, then I no longer want Column (I) to color scale - so go back to the original color. I have attached an example chart for your reference.

    For clarity:

    If the Complete Date column (L) is not blank, then I want the ETA column (I) to color scale - with darker colors for dates that are approaching, ligher colors for dates that are further away. The darker colors will be an indication that we are expecting a delivery soon, and the lighter colors will indicate that we won't be expecting that for a few weeks/months from now. I just want these cells to change color to trigger me to be on the look out for these orders. If the order is complete (column L <>""), then I no longer need it to change color because I do not need a trigger for that order. I hope this helps.

    Please let me know if you have any questions.

    Thank you!!!!!!!
    Attached Files Attached Files

  11. #11
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    24,928

    Re: Conditional Formatting - change color based on how far/close date is from today

    For 1

    =AND(TODAY()>$A3+5,$F3="")

  12. #12
    Registered User
    Join Date
    08-03-2015
    Location
    United States
    MS-Off Ver
    Excel 2013
    Posts
    38

    Re: Conditional Formatting - change color based on how far/close date is from today

    Quote Originally Posted by JohnTopley View Post
    For 1

    =AND(TODAY()>$A3+5,$F3="")
    This one worked - thank you SO MUCH!! I truly appreciate your help.

  13. #13
    Registered User
    Join Date
    08-03-2015
    Location
    United States
    MS-Off Ver
    Excel 2013
    Posts
    38

    Re: Conditional Formatting - change color based on how far/close date is from today

    Quote Originally Posted by JohnTopley View Post
    For 3

    =AND(I3<>"",I3>TODAY()-5) lighter

    =AND(I3<>"",I3>TODAY()-3) darker
    Hello John,

    For this one, do I just enter this as a new rule "Use a formula to determine which cells to format" for the rule type? And do I enter this rule in Column I? Because it didn't work.

    Also, is there a way to get these to color scale with gradient colors?

  14. #14
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    24,928

    Re: Conditional Formatting - change color based on how far/close date is from today

    Complete Date column (L) is not blank, then I want the ETA column (I) to color scale ......

    If the order is complete (column L <>""), then I no longer need it to change color
    These are contradictory

    For darker ( within 10 days)

    =AND(L3="",I3<>"",I3<=TODAY()+10)

    Within 20 days (Lighter)

    =AND(L3="",I3<>"",I3<=TODAY()+20)


    FORMAT using Fill Effects/Gradient

    The "darker" CF must be placed before the "lighter" CF
    Last edited by JohnTopley; 12-13-2015 at 04:05 AM.

+ 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. Replies: 13
    Last Post: 07-09-2014, 04:10 PM
  2. Replies: 5
    Last Post: 12-07-2013, 02:21 PM
  3. Replies: 10
    Last Post: 06-19-2013, 05:28 PM
  4. Replies: 6
    Last Post: 03-20-2012, 12:08 AM
  5. Change worksheet tab color based on today's date
    By james4026 in forum Excel General
    Replies: 3
    Last Post: 03-31-2010, 06:55 PM
  6. Conditional formatting based on date - not today's!
    By HarveyDickinson in forum Excel General
    Replies: 1
    Last Post: 09-11-2009, 05:46 AM
  7. Change row color based on TODAY>=specific date
    By nhentsch in forum Excel General
    Replies: 1
    Last Post: 09-23-2008, 04:12 AM

Tags for this Thread

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