SaraWitch
Active Member
- Joined
- Sep 29, 2015
- Messages
- 370
- Office Version
- 365
- Platform
- Windows
Hello peeps,
I have a start date in A2, a due date in B2 and a completion date in C2. The due date automatically updates when a completion date is entered in C2 (as this is a recurring annual review).
I would like the row to turn red if there is a due date that has passed and C2 is either blank and today's date is greater than due date or there is a date last completed (date in C2) and today's date is greater than (new) due date.
I started trying to do a formula for conditional formatting on just the dates in A2 to C2, but couldn't work it out. I then thought that by adding a status column I could more easily apply conditional formatting but now I'm tying myself up in knots to get the correct status returning (example below)!
I hope this makes sense and I would be really grateful if anyone can help me out... Ta very muchly!![Smile :) :)](data:image/gif;base64,R0lGODlhAQABAIAAAAAAAP///yH5BAEAAAAALAAAAAABAAEAAAIBRAA7)
I have a start date in A2, a due date in B2 and a completion date in C2. The due date automatically updates when a completion date is entered in C2 (as this is a recurring annual review).
I would like the row to turn red if there is a due date that has passed and C2 is either blank and today's date is greater than due date or there is a date last completed (date in C2) and today's date is greater than (new) due date.
I started trying to do a formula for conditional formatting on just the dates in A2 to C2, but couldn't work it out. I then thought that by adding a status column I could more easily apply conditional formatting but now I'm tying myself up in knots to get the correct status returning (example below)!
I hope this makes sense and I would be really grateful if anyone can help me out... Ta very muchly!
¦ MrExcel Queries.xlsm | |||||||||
---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | |||
1 | Start date | Review due date | Date completed | Status | Status should be | ||||
2 | 01/06/2022 | 01/06/2025 | 01/06/2024 | FALSE | Pending | Would like row to turn red if there is a due date that has passed and C2 is either blank and today's date is greater than due date or there is a date last completed (date in C2) and today's date is greater than (new) due date | |||
3 | 01/06/2023 | 31/05/2024 | FALSE | Overdue | |||||
4 | 01/06/2022 | 01/06/2024 | 02/06/2023 | FALSE | Overdue | ||||
Due Date |
Cell Formulas | ||
---|---|---|
Range | Formula | |
B2:B4 | B2 | =IF(A2="","",IF(C2="",A2+365,C2+365)) |
D2:D4 | D2 | =IF($A2="","",AND(C2="",TODAY()<B2,"Pending,AND(c2="",TODAY(0>B2,""Overdue",AND(C2>0,TODAY()>B2,"Overdue"))) |
Cells with Conditional Formatting | ||||
---|---|---|---|---|
Cell | Condition | Cell Format | Stop If True | |
A2:G4 | Expression | =$D2="Overdue" | text | NO |