Hi All,
I have a task tracker which I would like to add conditional formatting to highlight due dates which have passed. In column G I have a due date, in column D I have a status which can be the value of "Not Started", "In Progress", "Completed" or "Blocked".
I would like a formula to highlight the due date field in column G if, the due date has passed and the status in column D is NOT "Completed".
Any help would be greatly appreciated
Thanks
=AND( G2<>"", G2<today() , D2<>'completed")
for 2007, 2010 , 2013 , 2016 , 2019 or 365 Subscription excel version
Conditional Formatting
Highlight applicable range >>
G2:G100 - 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( G2<>"", G2<today() , D2<>"completed")
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
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.
Last edited by etaf; 04-27-2023 at 05:29 AM.
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.
Welcome to the forum.
Your CF rule for cell G1:
=AND(D1<>"",D1<>"Completed")
If you need help implementing this, then please provide a sample workbook.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
Hi Etaf, many thanks for your response. The statement is not quite correct as in Cell D, I can select the Status values of "Not Started", "In Progress", "Completed" or "Blocked". I would like to cell to highlight if the value entered in the Cell is "Not Started", "In Progress", or "Blocked". The date cell should not highlight if the date has passed and the status is "complete", only if its is one of the other statuses. Let me know if you need a sample workbook. Thanks
yes pleaseLet me know if you need a sample workbook.
Did you see post #3?
As I said, if you need help with the implementation, provide a sample workbook.
If it needs applying to a range, then you need to fix the lookup cell:Originally Posted by etaf
=AND( G2<>"", G2<today() , $D2<>"completed")
Hi All, many thanks for the quick responses. I have uploaded a sample workbook, as the formatting is not quite right yet. Really appreciate your support
No, there's no workbook attached anywhere. Please try again.
Oops, Missed a step. Hopefully you can see the sample sheet now.
Sample Sheet CF.xlsx
Rule for G3:
=AND(TODAY()>G3,G3<>"",$D3<>"Completed")
Applies to: $D3:$D5000
Last edited by AliGW; 04-27-2023 at 06:02 AM. Reason: Workbook added.
i have used
=AND( $G3<>"", $G3<TODAY(), $D3<>"completed")
The $ are in case you want to choose a range , as mentioned above
I have added a couple of dates
for 2007, 2010 , 2013 , 2016 , 2019 or 365 Subscription excel version
Conditional Formatting
Highlight applicable range >>
G3:G100 - 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( $G3<>"", $G3<TODAY(), $D3<>"completed")
Format [Number, Font, Border, Fill]
choose the format you would like to apply when the condition is true
OK >> OK
Thanks all, Issue now resolved. I'll close the thread. Thanks to all that contributed.
you are welcome
Glad to have helped.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks