+ Reply to Thread
Results 1 to 12 of 12

Question about Conditional Formatting

  1. #1
    Registered User
    Join Date
    07-06-2020
    Location
    New York
    MS-Off Ver
    Excel
    Posts
    6

    Question about Conditional Formatting

    I have an excel sheet for tracking projects and I'm basically I'm trying to get my actual task finish date to highlight green or red if it hits or doesn't hit the target date. I want this to go for the entire columns that I assign it to. I've been playing around with it for a few hours and I can't seem to get anything to work. If someone can help guide me or help me get this to work that would be amazing, thanks.

  2. #2
    Valued Forum Contributor
    Join Date
    04-24-2020
    Location
    Woodbridge, VA
    MS-Off Ver
    2016
    Posts
    434

    Re: Question about Conditional Formatting

    Hi - can you attach your file?

  3. #3
    Registered User
    Join Date
    07-06-2020
    Location
    New York
    MS-Off Ver
    Excel
    Posts
    6

    Re: Question about Conditional Formatting

    Here it is. The file I attached is just a simple basic sheet with a few due dates and then actual completed dates and I just want to have the ones that were completed before or on the due dates automatically colored green and the ones that weren't to be red. This way, whenever completion dates are added to the sheet, they would change colors themselves based on their proposed due dates. I just want to learn how to do it in basic forms first so I could add it after to the big file. If I know how to do it for this sheet then it will be easy. I've tried using DATEDIF or checkbox with IF function but can't seem to get it right. Any help is appreciated
    Attached Files Attached Files
    Last edited by nyyankeesrd; 07-06-2020 at 03:15 PM.

  4. #4
    Valued Forum Contributor
    Join Date
    04-24-2020
    Location
    Woodbridge, VA
    MS-Off Ver
    2016
    Posts
    434

    Re: Question about Conditional Formatting

    OK - so I see your Conditional Formatting you have when you have On Time formatted to Green and Late formatted to Red. So, correct me if I'm wrong, but you basically want that Column D to be green or red where you don't have to type in On Time or Late based on dates in Columns B and C?

  5. #5
    Registered User
    Join Date
    07-06-2020
    Location
    New York
    MS-Off Ver
    Excel
    Posts
    6

    Re: Question about Conditional Formatting

    Yes either that or if column C can have its color changed that would be good too so that I wouldn't need an extra column, Whatever is easier/plausible. Columns B and C however are indeed the two dates i'm comparing

  6. #6
    Valued Forum Contributor
    Join Date
    04-24-2020
    Location
    Woodbridge, VA
    MS-Off Ver
    2016
    Posts
    434

    Re: Question about Conditional Formatting

    I had something in mind, but it didn't work because some of the dates are the same as in another row.

  7. #7
    Registered User
    Join Date
    07-06-2020
    Location
    New York
    MS-Off Ver
    Excel
    Posts
    6

    Re: Question about Conditional Formatting

    Well I just realized that for the file I sent, the bottom 3 rows are extras that I used as testing and they can be removed, which would make there not be any duplicates. On the real file however, there are multiples

  8. #8
    Valued Forum Contributor
    Join Date
    04-24-2020
    Location
    Woodbridge, VA
    MS-Off Ver
    2016
    Posts
    434

    Re: Question about Conditional Formatting

    You can try this in D4 then just copy down.

    =IF(B4=C4,"On time","Late")

    Then it will just use your Conditional Formatting to color D either Green or Red.

    Whatever I tried in Conditional Formatting in Column C did not work. Hope this will do.

  9. #9
    Registered User
    Join Date
    07-06-2020
    Location
    New York
    MS-Off Ver
    Excel
    Posts
    6

    Re: Question about Conditional Formatting

    That's a good idea, however It won't work properly if the completion date is earlier than the intended due date since they aren't equal and it gives it "late" and red colored marking in column D. The same problem goes for using DATEDIF in that way since it won't work if it's negative (aka earlier). Not really sure what to do here and I hope someone can help. I might have to go to for help in vba or using macros but was hoping it could be down somehow with conditional formatting

  10. #10
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,424

    Re: Question about Conditional Formatting

    Try the following modification to mikeava's formula: =IF(B4>=C4,"On time","Late")
    Let us know if you have any questions.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  11. #11
    Registered User
    Join Date
    07-06-2020
    Location
    New York
    MS-Off Ver
    Excel
    Posts
    6

    Re: Question about Conditional Formatting

    sorry for the late response, but yes that actually works perfectly thank you. I'm surprised that slipped over my head so I appreciate you pointing it out.

  12. #12
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,424

    Re: Question about Conditional Formatting

    You're Welcome and thank you for the feedback. Please take a moment to mark the thread as 'Solved' using the thread tools menu above your first post. I hope that you have a blessed day.

+ 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. Conditional Formatting Question
    By kriscons in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 04-19-2017, 10:28 AM
  2. [SOLVED] Conditional formatting question
    By weaverswonders6 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 03-24-2014, 04:25 PM
  3. [SOLVED] Conditional Formatting Question
    By PineyWoods in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-14-2012, 10:04 PM
  4. Conditional formatting question
    By dumediat in forum Excel General
    Replies: 3
    Last Post: 07-27-2012, 12:46 AM
  5. IF THEN or Conditional Formatting question
    By Rivanthe5th in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 07-25-2012, 01:23 PM
  6. Another Conditional Formatting Question
    By 1320_Life in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 07-22-2006, 05:10 PM
  7. [SOLVED] Conditional formatting question
    By Dodo in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 09-06-2005, 02:05 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