+ Reply to Thread
Results 1 to 8 of 8

Conditional Formatting Color for one cell's dates based on another cells date

  1. #1
    Registered User
    Join Date
    08-19-2021
    Location
    Venice, Italy
    MS-Off Ver
    2016
    Posts
    14

    Question Conditional Formatting Color for one cell's dates based on another cells date

    Good evening,

    I am working on a tracker for work and am running into an issue that google has not been able to help me with. I want to conditional format using colors (red, yellow, green) for a cells date, based on another cells date. The two dates are based on a year for being overdue. If the date it was inputted on shows 9/3/2021 then it is set to expire on 9/3/2022. The example is listed below

    Column F / Column G

    F1: Posicheck On | G1: Test Due
    F2: 9/3/2021 | G2: 9/3/2022

    I want to turn cell G2 turn red when it passes the date in F2, turn yellow when it is within 30 days of F2, and remain green if it has more than 30 days till it passes F2. If need be I can also post a screenshot, I just unfortunately cannot at this time because I am not at work. I greatly appreciate whatever assistance can be provided as I worked on this for about 4 hours with no luck.

  2. #2
    Forum Guru HansDouwe's Avatar
    Join Date
    06-21-2022
    Location
    Nederland
    MS-Off Ver
    365 V2403 (Build 17330.20000)
    Posts
    6,341

    Re: Conditional Formatting Color for one cell's dates based on another cells date

    Apply 2 conditional rules with formulas on cell G2:

    1st: =(F2>G2) --> Format colour cell RED
    2nd: =(F2+30>G2) --> Format color cell YELLOW

    If you attached a sample workbook, I can show you this solution.
    Last edited by HansDouwe; 08-10-2022 at 06:17 PM.

  3. #3
    Registered User
    Join Date
    08-19-2021
    Location
    Venice, Italy
    MS-Off Ver
    2016
    Posts
    14

    Re: Conditional Formatting Color for one cell's dates based on another cells date

    Book1.xlsx

    As requested.

  4. #4
    Forum Guru HansDouwe's Avatar
    Join Date
    06-21-2022
    Location
    Nederland
    MS-Off Ver
    365 V2403 (Build 17330.20000)
    Posts
    6,341

    Re: Conditional Formatting Color for one cell's dates based on another cells date

    Thx for adding a sample sheet,

    Here is an example

    First I made all cells green and after that I created the new conditional format rules.
    If you want to see or change this rules
    * Select cell G2:G5
    * Click Conditional formatting
    * Click Manage rules
    * Select a rule
    * Click Edit Rule
    Now you can see or change te rule.

    Is this useful to you?
    If you have any questions, comments or feed-nack, please let know.
    Attached Files Attached Files
    Last edited by HansDouwe; 08-10-2022 at 06:19 PM.

  5. #5
    Registered User
    Join Date
    08-19-2021
    Location
    Venice, Italy
    MS-Off Ver
    2016
    Posts
    14

    Re: Conditional Formatting Color for one cell's dates based on another cells date

    Good afternoon,
    Is there a way of doing that without changing the formula piece in G2? It has to remain the way it is to give it the 365 days past G2. Unfortunately the way provided will not work with the way the forumula for G2 was changed.

  6. #6
    Forum Guru HansDouwe's Avatar
    Join Date
    06-21-2022
    Location
    Nederland
    MS-Off Ver
    365 V2403 (Build 17330.20000)
    Posts
    6,341

    Re: Conditional Formatting Color for one cell's dates based on another cells date

    I changed the formula otherwise I couldn't test the conditional formatting.
    The formula was G2: =F2+365. In that case, F2 is always passed G2 and therefore always red.
    You can reset the formula of G2: =F2+365, but then G2 will always turn red.

  7. #7
    Registered User
    Join Date
    08-19-2021
    Location
    Venice, Italy
    MS-Off Ver
    2016
    Posts
    14
    Quote Originally Posted by HansDouwe View Post
    I changed the formula otherwise I couldn't test the conditional formatting.
    The formula was G2: =F2+365. In that case, F2 is always passed G2 and therefore always red.
    You can reset the formula of G2: =F2+365, but then G2 will always turn red.
    Is there a way to conditional format the G cells based off the f cells then? So if it is within 30 days of f2, then g2 will turn yellow and if it overlaps then it turns red?

  8. #8
    Forum Guru HansDouwe's Avatar
    Join Date
    06-21-2022
    Location
    Nederland
    MS-Off Ver
    365 V2403 (Build 17330.20000)
    Posts
    6,341

    Re: Conditional Formatting Color for one cell's dates based on another cells date

    Have I interpreted it so well?
    If not, please give examples when you expect which color.
    Attached Files Attached Files

+ 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. Cell Color change based off multiple cells conditional formatting
    By gulonraid in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 08-20-2021, 01:18 PM
  2. [SOLVED] Conditional Formatting to color one cell based on a month from a date in another cell
    By aasher1 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 05-07-2018, 10:02 AM
  3. [SOLVED] Conditional Formatting formula to color one cell based on a date range in another cell
    By katiedee1625 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 04-27-2018, 08:52 AM
  4. Replies: 5
    Last Post: 12-07-2013, 02:21 PM
  5. [SOLVED] Conditional Formatting (changing color) in one cell based on text in two cells
    By Chadyoung1 in forum Excel Formulas & Functions
    Replies: 13
    Last Post: 02-14-2013, 04:26 AM
  6. 3 Color Conditional Formatting Based on Various Expiration Dates
    By sandmankuwait in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-18-2013, 11:11 AM
  7. Replies: 8
    Last Post: 01-02-2013, 02:25 PM

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